Changing of a date once it has passed

Question

Hello Smartsheet community,

I am looking for a formula where a cell automatically changes to the next date once the current day has passed. For example:

If the date cell currently has a "12/3/2022", I want to replace it with next month's "1/3/2022" in the same cell.

I am not sure if this would be an automation or something that you can do as a formula. Just wanted to have some insight. Please let me know at your earliest convenience. Thank you!

Paul Newcome · Accepted Answer

Do you have a "base date" somewhere that would be used as a starting point? Or at least the day of the month would be all you really need. The formula though would look something like this:

=IF(DAY(TODAY())> 3, IFERROR(DATE(YEAR(TODAY()), MONT(TODAY()) + 1, 3), DATE(YEAR(TODAY()) + 1, 1, 3)), DATE(YEAR(TODAY()), MONTH(TODAY()), 3))