Dynamic Date that Advances | Resets in Google Sheets

How to get a dynamic date that advances/resets on a particular day in Google Sheets? I am going to explain that in this tutorial and of course you will get the formula for the same.

Before going to the formula section, can you explain to me what is the so-called dynamic date that advances/resets monthly or in a particular duration?

Why not? Here you go!

You can automatically advance a date in a cell in different ways. That’s why it’s called a dynamic date. Here I am taking into account the below three scenarios.

Dynamically Advance a Start Date on a Particular Day in Every Month

Scenario 1:

Suppose, today is 11-February-2019. I have the date 05-February-2019 in cell A1.

When today’s date reaches 05-March-2019, I want the date in cell A1 to be automatically changed to 05-March-2019.

Then the date must remain the same until today’s date reaches 05-Apr-2019 and continue the cycle throughout the year.

Reset a Date Yearly

Scenario 2:

It’s similar to the above scenario. Here the date should reset every year.

Assume I have the date 10-April-2019 in a cell (cell A4) and today is 9-April-2020. Tomorrow the date in cell A4 must be reset to 10-April-2020 and remain so until 10-April-2021. Then reset again.

Dynamically Advance a Date on Every ‘n’ Day

Scenario 3:

Here ‘n’ can be 7 days (every week), 14 days (every fortnight), or 30 days.

In the below examples, please consider the date in cell A1 as 05-February-2019 and today’s date as 11-February-2019.

Reset Date Dynamically in Every Week:

The date in A1 should change to 12-February-2019, then to 19-February-2019 and so on based on today’s date reaching on that particular point.

Again for your reference, the current date is 11-Feb-2019. So the date in A1 should automatically be changed to 12-Feb-2019 by tomorrow.

Reset Date Dynamically in Every Fortnight:

Every fortnight means every two weeks. So the date should automatically be advanced as per the following cycle; 19-February-2019, 05-March-2019, then 19-March-2019 and so on.

Reset Date Dynamically in Every 30 Days:

In this, the date in cell A1 must advance 30 days, that in every 30-days cycle. So the date must automatically reset on 07-March-2019, 06-Apr-2019, 06-May-2019 and so on.

Now let me share the formulas with you. There are only three formulas that you may want to use or learn. One each for the above three scenarios.

Refer my Function Guide to understand the functions in use in my formulas below.

The Formula to Get a Dynamic Date that Advances or Resets in Google Sheets

I have given enough explanations to the dynamic date that advances/resets in Google Sheets. Now time to try the formulas one by one.

Google Sheets Formula to Auto Advance a Start Date in Every Month

You can use all the below formulas in two ways. Either using the base/start date as a reference to any cell or within the formula. Here I am following the former approach.

I am putting the start date in cell A1 and using the formula in cell A2. So the date in cell A2 will auto-advance/reset on the set cycle (I think the reset is the better word to use).

=if(A1<today(),if(day(today())>=day(A1),EOMONTH(today(),-1)+day(A1),EOMONTH(today(),-2)+day(A1)),A1)

Here I have extensively used Google Spreadsheets’ Date functions and logical IF.

Formula Logic: Some of you may be interested to know the logic of this formula. So here is the explanation.

I hope this generic formula is sufficient in this case.

Except for the Outer IF:

=if(day of today is >=day of A1, end of the day of the last month+day of A1, else end of the day of the month before the last month+day of A1)

The Role of the Outer IF:

=if(A1<today(),above_logic,A1)

Reset a Date in an Yearly Cycle – Formula

Cell A4 contains the date to reset in an yearly cycle (every year on the same date). Use this formula in cell B5.

=if(A4<today(),IF(and(year(today())>=year(A4),if(month(today())=month(A4),day(today())>=day(A4),),month(today())>=MONTH(A4)),date(year(today()),month(A4),day(A4)),date(year(today())-1,month(A4),day(A4))),A4)

I am skipping the explanation. It’s similar to the above monthly resetting formula. Here other than ‘day’, I have used ‘month’ and ‘year parameters. And also some more changes are there.

Since there are only logical functions and date functions in the formula, you may be able to read it. Further, you will get my example sheet in the last part of this tutorial.

Google Sheets Formula to Auto Reset a Start Date Every ‘n’ Day

Here things are a little simpler. The ‘n’ day can be any number of days. Most commonly we may use every 7 days, 14 days, or 30 days.

The Formula to Advance a Date in Every 30 Days Cycle

=TODAY()-MOD(TODAY()-A1,30)

This Google Sheets formula will advance a date by 30 days. Here is the logic.

The key in this formula is the MOD mathematical function. The MOD function returns a remainder after a division operation.

MOD(dividend, divisor)

The dividend in the MOD function is the number of days that we get by subtracting the date in cell A1 with the current date. The divisor is the 30 days cycle.

Example:

Cell A1 contains 05-Dec-2018 and the current date is 11-Feb-2019. The dividend in the MOD formula would be 68. So the below formula would return 8 which is the remainder.

=MOD(TODAY()-A1,30)

Just subtract this remainder from today’s date to get a dynamic date that advances every 30 days.

The Formula to Auto Advance a Date Every Fortnight

Here use the above same formula. Just change the divisor 30 in the MOD formula with 14.

=TODAY()-MOD(TODAY()-A1,14)

The Formula to Auto Advance a Date Every Week

=TODAY()-MOD(TODAY()-A1,7)

Here in the below image, I have demonstrated all the pattern of the progress of the dynamic dates.

Dynamic Date that Advances/Resets in Google Sheets

Here is one real-life use of how the dynamic reset date works.

Reset Timeline Start and Finish Dates

You have learned how to get a dynamic date that advances/resets in Google Docs Sheets. That you can mainly use to reset your timeline activity start dates conditionally.

Many people depend on premium online project management software products like WRIKE to scheduling and keeping track of their team’s work.

As you may know, the Timeline (Gantt) charts are part of almost every scheduling activity. Of course, the above premium product also offers Timeline charts – How to create a Timeline chart using Wrike.

Even though you can’t make a fully flexible timeline chart in Google Sheets like the mentioned project management software does, you can use the built-in Sparkline function to create a timeline chart in Sheets – Gantt Chart using Sparkline in Sheets.

In such a simple and very basic timeline chart, there will be of course activity start dates and end dates that you can automate.

Real-Life Example to Dynamic Start and End Dates that Advances/Resets

The below example shows the different supply start and end dates for different projects. I have scheduled the supply of materials as below.

Assume I have updated the “Status” column E today, i.e. on 12-Feb-2019. Just go through the “Finish Date” column and “Status” column.

In the two highlighted rows, i.e. rows # 3 and 4, the finish date is <=today and the status are not completed (“In progress” and “Hold”). That means that activities are still live and I must change the start and end dates.

I only need the stipulated number of days in column D to complete the job. The jobs are delayed only due to the late starting. So I want to advance the start and end dates keeping the ‘No. Days’ in column D (duration) the same.

Here see how I am making use of the dynamic date that advances/resets feature in project start date.

The Formula to Reset Timeline Start and Finish Dates in Sheets

Copy column B (Start Date) and D (No. of Days) values to column H and I respectively. Then delete the dates (Finish Date) in column C and also in Column B (Start Date).

Then use the below formula in Cell B2 and drag down until you reach B7.

=if(and(H2+I2<today(),E2<>"Completed"),TODAY()-MOD(TODAY()-H2,I2),H2)

Next use this formula in cell C2 and drag down.

=B2+D2

See how the project start and finish dates advance based on conditions. You can refer the formula in cell B2 to know the conditions used.

Here is the generic version of the said formula to understand it:

=if(and(Finish Date<today(),Status<>"Completed"),Dynamic Reset Formula,Default Start Date)

That’s all about how to get a dynamic date that advances/resets in Google Sheets. Hope you have enjoyed the stay!

Sample Sheet

Additional Resources:

Get a Dynamic Date that Advances | Resets in Google Sheets