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