Create a Dynamic Date That Advances or Resets in Google Sheets

Are you looking for a way to create a dynamic date that automatically advances or resets on a specific day in Google Sheets? In this tutorial, I’ll show you how to build formulas that accomplish just that—perfect for setting up auto-updating dates for tasks, renewals, reminders, and more.

Before diving into the formulas, let’s first understand what a dynamic date means in the context of Google Sheets.

What Is a Dynamic Date in Google Sheets?

A dynamic date in Google Sheets is a date that changes automatically based on today’s date. You can configure it to:

Advance monthly on a specific day,
Reset annually on the same date,
Or update every “N” days (e.g., weekly, biweekly, etc.).

This is especially useful for managing recurring events, renewal schedules, and automated workflows in spreadsheets.

1. Dynamic Date That Advances or Resets Monthly

Let’s say today is 11-February-2019, and the date in cell A1 is 05-February-2019. You want that date to automatically change to 05-March-2019 when today’s date hits March 5th—and continue this pattern monthly.

Formula (Monthly Reset)

=LET(dt, A1, 
   IF(
      dt >= TODAY(), 
      dt, 
      DATE(
         YEAR(TODAY()), 
         MONTH(TODAY()) - (DAY(TODAY()) < DAY(dt)), 
         DAY(dt)
      )
   )
)

If you don’t want to use a helper cell, simply replace A1 with your specific date like DATE(2019, 2, 5).

How the Formula Works

This Google Sheets date formula works by:

Checking if the date in A1 is greater than or equal to today. If so, it returns that date.
If not, it calculates the next monthly instance of the given day using:
DATE(YEAR(TODAY()), MONTH(TODAY()) - (DAY(TODAY()) < DAY(dt)), DAY(dt))

Here’s what’s happening:

YEAR(TODAY()): Fetches the current year.
MONTH(TODAY()) - (DAY(TODAY()) < DAY(dt)): Adjusts the month back by one if today’s day is earlier than the target day.
DAY(dt): Grabs the day from the original date.

This formula gives you an automated monthly reset based on the current date—a powerful dynamic scheduling tool in Google Sheets.

2. Dynamic Date That Resets Annually

Let’s now configure a dynamic annual date reset in Google Sheets. Suppose the original date in cell A1 is 10-April-2019, and today’s date is 9-April-2020. The formula will show 10-April-2020 the next day. Once today reaches that date, the formula will automatically update to 10-April-2021, and continue resetting every year.

Formula (Annual Reset)

=LET(dt, A1, 
   IF(
      dt>TODAY(), 
      dt, 
      DATE(
         YEAR(TODAY()) - (DATE(YEAR(TODAY()), MONTH(dt), DAY(dt)) > TODAY()), 
         MONTH(dt), 
         DAY(dt)
      )
   )
)

Replace A1 with a static DATE(year, month, day) if desired.

This formula creates a dynamic reset of an annual date. It keeps showing the most recent recurrence of a specific date—resetting every year based on today’s date.

Formula Logic Breakdown:

DATE(
   YEAR(TODAY()) - (DATE(YEAR(TODAY()), MONTH(dt), DAY(dt)) > TODAY()), 
   MONTH(dt), 
   DAY(dt)
)

DATE(YEAR(TODAY()), MONTH(dt), DAY(dt)): builds the event date for the current year.
> TODAY(): checks if the current year’s event is still in the future.
If true, subtract 1 from the year → use last year’s event.
If false, use the current year’s date → next reset will happen next year.

Use Cases

This dynamic annual date reset is perfect for:

Automatically tracking recurring yearly events
Rolling reminders for dates like:
- Birthdays
- Annual reviews
- Subscription renewals
- Anniversaries

You don’t need to manually update the date each year—Google Sheets will do it for you.

3. Dynamic Date That Resets Every N Days

You might want the date to automatically reset every N days, like 7 (weekly), 14 (biweekly), or 30 (monthly-like cycle). Let’s cover all three.

Assume your original date in A1 is 05-February-2019, and today’s date is 11-February-2019.

Formula (Reset Every N Days – e.g., Weekly)

=LET(dt, A1, IF(dt<TODAY(), TODAY()-MOD(TODAY()-dt, 7), dt))

For a custom interval, change 7 to any number like 14 (fortnight) or 30.

How It Works

TODAY() - dt: Calculates how many days have passed since the original date.
MOD(TODAY() - dt, 7): Finds the remainder after dividing that by 7 (or N).
Subtracting the remainder gives the most recent reset date based on the interval.

Weekly Auto-Updating Date in Google Sheets

If today is 11-Feb, the formula will update A1 to 12-Feb by tomorrow (the next weekly instance).

Biweekly (Every 14 Days)

Change the 7 in the formula to 14:

=LET(dt, A1, IF(dt<TODAY(), TODAY()-MOD(TODAY()-dt, 14), dt))

Every 30 Days

Change the interval to 30:

=LET(dt, A1, IF(dt<TODAY(), TODAY()-MOD(TODAY()-dt, 30), dt))

This method gives you a powerful way to automate task cycles or track progress in fixed intervals in your Google Sheet.

Looking to expand your spreadsheet skills? Check out these related guides: