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.
Next working day calculation
Context: I've a Smartsheet that, given an agile software release number in the form of Year.Week.SubVer that calculates the release date based upon the first Monday of the Year (SubVer = Day of week, 1=Monday, 2=Tuesday), so 2015.18.1 = 4/27/2015 (and 2015.18.2 = 4/28/2015 etc)
What I'd like to do is be able to somehow finangle NetWorkDays (or something similar) into this calculation so, for example Release 2015.22.1 would actually fall on 5/26/2015 (Tuesday) because Monday 5/25 is a Federal Holiday (Memorial Day).
For better of for worse, this is essentially the main calculation I use (with _SubVer being a hidden column which I'm currently using until I get this all cleaned up into a bigger formula):
=IF(ISBLANK([Release Number]1), "", (VALUE(MID([Release Number]1, 6, 2)) * 7) + (_SubVer1 - 1) - 14)
Which for 2015.18.1 gives me 122 which I then add to Date(2015, 1, 5) to give me 4/27/2015.
Any ideas?
My fear is that the standard functionality is to just exclude Saturday and Sunday and that my only choice is to use the HolidayCellX reference which means that I have to embed the Federal Holidays into the same sheet as, last I looked, NetWorkDays() couldn't reference another worksheet...
Comments
-
Hi Rachel, I tried to work this out myself and had a colleague (a formula guru) look at this too, but we have not been able to figure out how to make it exclude non working days. I will keep thinking about it and if I come up with anything, I’ll let you know!
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives