If you are like me, you love making spreadsheets for almost any solution. I especially like making spreadsheets to track progress on projects. Weight loss goals? Marathon training? Social media metrics? Yeah. Spreadsheets are awesome.
When creating your progress calendar, how do you get a whole year’s worth of Mondays inputted into your sheet? A rookie mistake is to input data line by line when there is a perfectly acceptable way to create rows and rows of sequential data with a simple formula.
#1 Rule of Excel: There is pretty much ALWAYS a way to get it done with a formula.
Here’s how to populate sequential dates in Excel for a day, month, year, or even a whole decade’s worth of rows:
You can add (or subtract) any combination of days, months, and/or years to the initial date, by editing this formula:
=DATE(YEAR(first_date)+qty_year,MONTH(first_date)+qty_month,DAY(first_date)+qty_day)
- first_date: Your fixed start date (mm/dd/yyyy) or cell with your start date (B1).
- qty_year: The amount of years to be added or subtracted.
- qty_month: The amount of months to be added or subtracted
- qty_day: The amount of days to be added or subtracted.
And that’s it! In my example, I only needed to add a month so I left the add on values for “year” and “day” at zero. Of course you can also use “-1” in those spots if you need to do a little time travel in reverse.
Here are a few examples to give you some ideas:
Open up a brand new spreadsheet now and give this formula a try. The possibilities are endless once you start making complex computations. Need each row to show you +1 year, +5 weeks and +4 days into the future? I can’t imagine why you would need to, but you can do it with this formula.
Bonus Tip: If you are adding a month and your start date is the 29th, 30th, or 31st of the month, expect for February to give you some trouble.
A quick fix for this is to use the 28th as your initial date instead. Every month has a 28th!
Of course, with Excel, there is always more than one way to skin a cat. But this method gives you a chance to really practice your formula creation skills and carefully examine how formulas actually work.
Let me know: How do you use spreadsheets in your daily life?