Posted on

How to populate sequential dates in Excel

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:

add a date to excel

=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:

Excel Tips and Tricks
Creating a monthly schedule? Add one month to each row.

 

Quarterly reports? Add a new row for every 3 months.
Quarterly reports? Add a new row for every 3 months.

 

Biweekly reports are easy. Just tell your formula to add 14 days (2 weeks) to each new row.
Biweekly reports are easy. Just tell your formula to add 14 days (2 weeks) to each new row.

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.
Excel tips and tricksA 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?

Leave a Reply

Your email address will not be published. Required fields are marked *