evaluate if a cell date is weekend and convert its date to next weekday

Options
sca
sca
edited 08/27/21 in Formulas and Functions

Hi All,

I am trying to add a number of days from a calculated cell to a date and deduce a new date (weekday).

I am calculating the number of days based on allocation and adding that to the current date to get a new date. I need help to evaluate the result of function in current cell and increment the date to next weekday.

For example:

Current date cell: I have current date cell as 08/28/2021(mm/dd/YYYY)

Calculated date :I have a no of days calculated in different cell - 1day

New date cell: I want to add the current date+ days calculated in a new cell and increment the day to a next available weekday if the result is weekend so the date should be (instead of 08/29 which is Sunday)


Thank you for your help

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @sca

    Here's one approach, assuming you want the next weekday to be Monday.

    This evaluates what Weekday the Current Date + your interval falls on. If it falls on a weekend, the formula makes an adjustment. If the date isn't a weekend, it keeps the adjusted date.

    =IF(WEEKDAY([Current Day column]@row + [Number Days Column]@row) = 7, [Current Day column]@row + [Number Days Column]@row + 2, IF(WEEKDAY([Current Day column]@row + [Number Days Column]@row) = 1, [Current Day column]@row + [Number Days Column]@row + 1, [Current Day column]@row + [Number Days Column]@row))

    Be sure to update the formula using your actual column names.

    cheers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!