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
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.
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.
-
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))
-
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))
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives