Recurring Due Date Formulas That Update When Date Passes

Tori Heath

Hello Smartsheet Community!

I have a sheet that we're using to track when recurring deliverables are due and I'm looking for a formula that can automatically set the due date for the current period (month or quarter) and ALSO recognize if the due date for the current period has passed that it should provide the next upcoming due date.

For example, as you can see I have the Project Closing Report set up with the formula =DATE(YEAR([Due Date]1), MONTH([Due Date]1), 1), but am missing the logic that would allow it to recognize that 6/01/21 is in the past, so it should now be showing 7/01/21. There are about 15 quarterly deliverables as well that I am hoping to sort this out for too.

As always, thank you SO much in advance.

Cheers and have a good weekend,

Tori

Bassam Khalil

Hi @Tori Heath

Hope you are fine, could you share an excel sample of your sheet (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Andrée Starå

Hi @Tori Heath

I hope you're well and safe!

There are a couple of ways to structure it.

Here's an example for the monthly one.

=IF(DAY($[Due Date]$1) > 1, DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1))

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

Would that example for the month work?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Tori Heath

Hello -

Yes, please find attached an Excel extract of the Smartsheet file. Though you cannot see the formulas that I already have in place.. so I will also make a copy of the Smartsheet, remove sensitive info and share you to it.

The formula that you shared @Andrée Starå for the monthly deliverable did work well!

A huge, sincere thank you very much in advance.

All the best,

Tori

Greenbacker Deliverables.xlsx