Round returned date to working day?

Options

I've been using a formula to calculate due dates, which are always 90 calendar days from the original date column. This is the formula: =IF([Resubmission Required?]@row = "true", [Date Decided (Approved/Rejected)]@row + 90, "n/a")

However, because we are required to calculate these dates in calendar days, many of the dates returned fall on a Saturday or Sunday. Is it possible to write a formula that not only calculates the "technical" due date (+90 calendar days) but will also round the date BACK to the last workday (Friday) if the returned date falls on a weekend? Happy to provide more details if needed.

Tags:

Best Answer

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓
    Options

    I tried to get fancy with it, and it's possible to calculate using mod(), substitute(), and a variety of other equations, but a simply replacement of the weeknumber with an appropriate negative ends up much simpler.


    =IF([Resubmission Required?]@row = "true", [Date Decided (Approved/Rejected)]@row + 90 + IF(weekday([Date Decided (Approved/Rejected)]@row + 90) = 1, -2, IF(weekday([Date Decided (Approved/Rejected)]@row + 90)= 7, -1, 0)) , "n/a")

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓
    Options

    I tried to get fancy with it, and it's possible to calculate using mod(), substitute(), and a variety of other equations, but a simply replacement of the weeknumber with an appropriate negative ends up much simpler.


    =IF([Resubmission Required?]@row = "true", [Date Decided (Approved/Rejected)]@row + 90 + IF(weekday([Date Decided (Approved/Rejected)]@row + 90) = 1, -2, IF(weekday([Date Decided (Approved/Rejected)]@row + 90)= 7, -1, 0)) , "n/a")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!