Formula that will update the new Year dates

Karina.Perez

Looking for a formula that will automatically update my reporting dates to 2021. Start date is the first day of the month, end date is the last day of the month. Quarter dates are based on first day in January, last day in March, ect.

Brett Robinson

You could try something like this:

=DATE(YEAR(TODAY()), 1, 1)

For the start date, you'll just change the month number (the first 1). For the end date, you'll change the month and the day number to the last day of whatever month you're in (day number is the second 1). Come January 1, 2021, all the years should flip over from 2020 to 2021. Couple examples:

July 1 Start Date: =DATE(YEAR(TODAY()), 7, 1)

July 31 End Date: =DATE(YEAR(TODAY()), 7, 31)

Another way you can do this at the end of the year is to create a second column to add a year. Below is the formula for the second column. Then copy and paste just the values back into the start date and end date columns.

=DATE((YEAR([Start Date]@row) + 1) + MONTH([Start Date]@row), DAY([Start Date]@row))

=DATE((YEAR([End Date]@row) + 1) + MONTH([End Date]@row), DAY([End Date]@row))

The second method requires you to do it every year. The first method should update at the start of every year with any effort after setting it up.