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.

«1

Answers

  • Gia Thinh
    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.


    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.

  • Paul Newcome
    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

  • 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
    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)))

  • 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
    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
    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.


    Gia Thinh Technology - Smartsheet Solution Partner.

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

  • 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
    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))


    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?

  • Gia Thinh
    Gia Thinh ✭✭✭✭✭✭

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


    Gia Thinh Technology - Smartsheet Solution Partner.

  • Paul Newcome
    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))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!