Next working day calculation

Rachel Ambler

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

Travis

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!