Creating date formula to move a cell on a specific period in time based on drop down options

Colette Hughes

I have a sheet of regular tasks that are either weekly, monthly, or annually in frequency.

I am trying to create a formula that will automatically create the next date based off the last scheduled date plus the frequency. For annually I am using the statement below:

=IF(Frequency@row = "Annually", DATE(YEAR([Last Scheduled Date]@row) + 1, MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row)))

For monthly I am using the statement below:

=IF(Frequency@row = "Monthly", DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row) + 1, DAY([Last Scheduled Date]@row)))

For weekly I am using the statement below:

=IF(Frequency@row = "Weekly", DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row + 7)))

The annual statement is great but the weekly and monthly statements only work until the date moves from December to January at which point it returns the incorrect date.

I have also only managed to get these formulas to work separately. As soon as I try to combine it doesn't work.

Any help would in combining the statements and fixing the December to January issue be much appreciated.

Gia Thinh

Hi Colette,

To make it simple, we can convert weekly by adding 7 days, monthly by adding 30 days, annually by adding 365 days to the "Last Scheduled Date".

Then try this formula:

=IF(Frequency@row = "Annually", [Last Scheduled Date]@row + 365, IF(Frequency@row = "Monthly", [Last Scheduled Date]@row + 30, IF(Frequency@row = "Weekly", [Last Scheduled Date]@row + 7)))

Hope that helps.

Colette Hughes

Thanks, this does work to a certain degree (and is much simpler) but a lot of our tasks are, say 1st monthly, so adding the 30 days doesn't work when your month has 31 days or 28 days. Same with the annual, it gets messed up with a leap year.

Paul Newcome

The monthly can be fixed like so:

IFERROR(DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row) + 1, DAY([Last Scheduled Date]@row)), DATE(YEAR([Last Scheduled Date]@row) + 1, 1, DAY([Last Scheduled Date]@row)))

Weekly can be fixed by moving the +7 to outside of the DATE function.

DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row)) + 7

Colette Hughes

Thanks Paul. How would I integrate the IFERROR into the formula below:

=IF(Frequency@row = "Monthly", DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row) + 1, DAY([Last Scheduled Date]@row)))

Also, how do I combine all those formulas so they are in one cell together?

Paul Newcome

The IFERROR(DATE(.....), DATE(.....)) would go in place of your existing DATE(.....) function.

To tie all three together, you would drop each into the "value if false" (third) portion of the preceeding IF.

=IF(this is true, output this, IF(that is true, output that, IF(other is true, output other)))

Colette Hughes

Sorry Paul but I don't understand that. I am fairly new to these formula's so I am not following what you are saying at all.

The IFERROR formula doesn't refer to the frequency field at all. How can I link the IFERROR, based on what is in the frequency field (which could be weekly, monthly, annually).

Paul Newcome

It is for the monthly formula. Just take the whole IFERROR piece and drop it into the existing monthly formula in place of your current DATE function.

Gia Thinh

Hi Colette,

Combining my formula and Paul's developed formula for monthly, I suggest this final one :

=IF(Frequency@row = "Annually", DATE(YEAR([Last Scheduled Date]@row) + 1, MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row)), IF(Frequency@row = "Monthly", IFERROR(DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row) + 1, DAY([Last Scheduled Date]@row)), DATE(YEAR([Last Scheduled Date]@row) + 1, 1, DAY([Last Scheduled Date]@row))), IF(Frequency@row = "Weekly", [Last Scheduled Date]@row + 7)))

Hope it works for you.

Colette Hughes

Thank you so much for your help Paul and Gia. This is awesome.

Paul Newcome

Happy to help. 👍️

Colette Hughes

I have one last question on this. For daily tasks, I want to skip and Saturday and Sunday. I have the formula below so far. How would I change it so that it jumps from a Friday to a Monday?

=IF(Frequency@row = "Annually", DATE(YEAR([Last Scheduled Date]@row) + 1, MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row)), IF(Frequency@row = "Monthly", IFERROR(DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row) + 1, DAY([Last Scheduled Date]@row)), DATE(YEAR([Last Scheduled Date]@row) + 1, 1, DAY([Last Scheduled Date]@row))), IF(Frequency@row = "Weekly", DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row)) + 7, IF(Frequency@row = "Quarterly", [Last Scheduled Date]@row + 92, IF(Frequency@row = "Daily", [Last Scheduled Date]@row + 1)))))

Gia Thinh

Hi Colette,

WEEKDAY Function | Smartsheet Learning Center

WEEKDAY fuction should be used in this case. The logic is : if date is Saturday (7) then date will jump to Monday by adding with 2. And if date is Sunday (1) then date will jump to Monday by adding with 1.

We will create a new collumn named "Modified Next Date" with below formula:

=IF(WEEKDAY([Next Date]@row) = 7; [Next Date]@row + 2; IF(WEEKDAY([Next Date]@row) = 1; [Next Date]@row + 1; [Next Date]@row))

Colette Hughes

So this meas I have to have two extra columns "weekday" and "modified next date" just to cater for daily tasks. Does the "weekday" field have to be populated manually for each task?

Gia Thinh

Weekday column can be deleted. It's just there for our reference while building the formula.

Paul Newcome

You don't need any extra columns or IF statements. Just a WORKDAY function:

=IF(Frequency@row = "Annually", DATE(YEAR([Last Scheduled Date]@row) + 1, MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row)), IF(Frequency@row = "Monthly", IFERROR(DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row) + 1, DAY([Last Scheduled Date]@row)), DATE(YEAR([Last Scheduled Date]@row) + 1, 1, DAY([Last Scheduled Date]@row))), IF(Frequency@row = "Weekly", DATE(YEAR([Last Scheduled Date]@row), MONTH([Last Scheduled Date]@row), DAY([Last Scheduled Date]@row)) + 7, IF(Frequency@row = "Quarterly", [Last Scheduled Date]@row + 92, IF(Frequency@row = "Daily", WORKDAY([Last Scheduled Date]@row, 1))))))