Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Due date PRIOR to weekend or holiday

ker9
ker9 ✭✭✭✭✭✭
edited 12/09/19 in Archived 2017 Posts

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.

Tags:

Comments

  • 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
    ker9 ✭✭✭✭✭✭
    edited 06/02/17

    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
    ker9 ✭✭✭✭✭✭
    edited 06/07/17

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

    HolidayFormulas.JPG

This discussion has been closed.