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

sca

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

Kelly Moore

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

https://help.smartsheet.com/function/weekday