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

Rachel Ambler
edited 12/09/19 in Archived 2015 Posts

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

  • Travis
    Travis Employee

    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! 

This discussion has been closed.