Creating date formula to move a cell on a specific period in time based on drop down options
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.
Answers
-
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.
Gia Thinh Technology - Smartsheet Solution Partner.
-
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.
-
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
-
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?
-
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)))
-
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).
-
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.
-
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.
Gia Thinh Technology - Smartsheet Solution Partner.
-
Thank you so much for your help Paul and Gia. This is awesome.
-
Happy to help. 👍️
-
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)))))
-
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))
Gia Thinh Technology - Smartsheet Solution Partner.
-
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?
-
Weekday column can be deleted. It's just there for our reference while building the formula.
Gia Thinh Technology - Smartsheet Solution Partner.
-
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))))))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.3K Get Help
- 364 Global Discussions
- 199 Industry Talk
- 428 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!