Now that we’ve gone through 4 test cases that show the calculations being performed, let’s go back to our “Plain English” translation and see how much cleaner it becomes.

Deciphering the Puzzle (v2.0)

Our condition:

If the “End Date” (number) is less than the “Start Date” (number), AND if the “Start Date” (number) minus the “End Date” (number) is greater than 1, do the following:

Count the number of days that separate the “Start Date” from the “End Date” then add 1 to that number.  From that number, subtract the number of days that separate the “Start Date” from the “End Date” plus 1, divided by 7, rounded down to the nearest 1 number, then multiplied by 2.  From this number, subtract 2 from the final result to take in account for weekend days to produce our final result.

If either or both, of the two parts of the condition are not “TRUE”, do the following:

Count the number of days that separate the “Start Date” from the “End Date” then add 1 to that number.  From that number, subtract the number of days that separate the “Start Date” from the “End Date” plus 1, divided by 7, rounded down to the nearest 1, then multiplied by 2 to produce our final result.

Still a little complex, but much easier to comprehend.

Summary

Sometimes, the obvious answer to a solution may be obscured by itself (as confusing as that very statement might sound, try going back to my original wording of the formula used in this series and attempt to read it aloud in a single breath – I can do it in 3 – barely, and I wasn’t sure what I had just read by doing it so quickly).

“DATEDIF” seems like the obvious choice for our problem, but since it’s rather literal in what it does, it counts each and every day in between two dates, including weekends, which is not exactly what we want.

In Excel, this is almost trivial because of how easy it is to come up with the solution, we just use “NETWORKDAYS”, plug in the start and end dates and voilà, we have an accurate count that magically excludes weekend days (again, another of the numerous functions they did not include in SharePoint for some reason). 

The unfortunate side of this is that even if you run the “Evaluate Formula” command within Excel on the “NETWORKDAYS” function, you can’t see each and every step being performed in the calculation to see just how it skips the weekend days in the end result.  This function obscures itself by not showing you “how” it does what it does (far too many Excel functions have this same limitation unfortunately).   But, given the functions available (within Excel, and SharePoint), we can attempt to “duplicate” what it’s doing by using several functions together that will “logically” return the exact same result, just through a much longer process.

Hopefully all of this wasn’t too confusing – it’s a hard formula to decipher, but as with all formulas, the best approach is to break it into chunks, determine what each chunk does independently, then merge them all back together and run through the entire thing with a series of test data.
Till next time…

- Dessie

Dessie Lunsford
Points-of-Sharing