Due date PRIOR to weekend or holiday

I have a one date upon which all other due dates are calculated.

We have holidays set up for the account.

I need the due dates to fall on the weekday PRIOR to the due date if it is a weekend or holiday. It appears the Workday and Networkday functions fall on the following week day, which doesn't work for me.

This formula calculates if the date falls on a weekend. If not a weekend, 45 days, if a Sunday 43 days, if a Saturday 44 days. How would I add in Holidays?

=IF(WEEKDAY([Start Date]17 + 45) = 1, [Start Date]17 + 43, IF(WEEKDAY([Start Date]17 + 45) = 7, [Start Date]17 + 44, [Start Date]17 + 45))

If the start date is 5/20/17, adding 45 days causes it to fall on 7/4/17. Since 7/3 and 7/4 are company holidays, I need the 45 day due date to be 6/30/17, the previous workday.

Thank you.

Find more posts tagged with

Formulas

Comments

Shaine Greenwood

Hello,

You can use the WORKDAY function for this: https://help.smartsheet.com/function/workday

You'd need to add the holiday dates to your sheet, possibly in a hidden column.

ker9

Thank you for your response, however, that doesn't work. It's not 45 workdays, it's 45 days, however, if the 45th day lands on a weekend or holiday, the due date is the business day prior.

This is how I do it in Excel where $B$5 is the start date and the word "holidays" in the formula is a named range of the holiday dates:

=IF($B$5 = "", "", $B$5+45-SMALL(IF(WEEKDAY($B$5+45-{0,1,2,3,4,5},2)<6,IF(COUNTIF(holidays,$B$5+45-{0,1,2,3,4,5})=0,{0,1,2,3,4,5})),1))

ker9

I think I've got this figured out.

Screenshot shows the layout, below are the formulas that force a 45 day due date to fall on the weekday prior taking weekends and holidays into account. (It's 45 days, not 45 workdays.)

Holidays start on row 5 and are always on a weekday

Weekday 1 = Sunday

Subtract Days formula:

=IF(WEEKDAY(Holidays5) = 2, 3, IF(WEEKDAY(Holidays5) > 2, 1))

New Due Date formula: (takes into account two holidays next to each other)

=IF(ISERROR(INDEX(Holidays$5:[Subtract Days]$22, MATCH(Holidays5 - [Subtract Days]5, Holidays$5:Holidays$22, 0), 2)), Holidays5 - [Subtract Days]5, ((Holidays5 - [Subtract Days]5) - INDEX(Holidays$5:[Subtract Days]$22, MATCH(Holidays5 - [Subtract Days]5, Holidays$5:Holidays$22, 0), 2)))

45 Day formula removing weekends & holidays

=IF(ISERROR(INDEX($Holidays$5:$[New Due Date]$22, MATCH(IF(WEEKDAY($[Start Date]$1 + 45) = 1, $[Start Date]$1 + 43, IF(WEEKDAY($[Start Date]$1 + 45) = 7, $[Start Date]$1 + 44, $[Start Date]$1 + 45)), $Holidays$5:$Holidays$22, 0), 3)), IF(WEEKDAY($[Start Date]$1 + 45) = 1, $[Start Date]$1 + 43, IF(WEEKDAY($[Start Date]$1 + 45) = 7, $[Start Date]$1 + 44, $[Start Date]$1 + 45)), INDEX($Holidays$5:$[New Due Date]$22, MATCH(IF(WEEKDAY($[Start Date]$1 + 45) = 1, $[Start Date]$1 + 43, IF(WEEKDAY($[Start Date]$1 + 45) = 7, $[Start Date]$1 + 44, $[Start Date]$1 + 45)), $Holidays$5:$Holidays$22, 0), 3))