Excel trick for counting days
Illustration by Joseph Herren

I am a workforce manager, which means I need to count days for many reasons: payroll days for my staffing models; working days, so that I don’t apply shrinkage to people on vacation or FMLA; and index days, so I don’t treat a month with 10 weekend days the same way I would treat a month with eight weekend days.

When it’s time to count weekdays only, I use the NETWORKDAYS function in Excel. It’s super easy and only needs three entries: start date, end date, and the holidays in that period.

WFM Counting Days with Excel
Click image for larger view

There is a little trick to making it work: Each of the entries must be the Gregorian serial date number, not the MM-DD-YYYY version. If you try to type in the MM-DD-YYYY number in the Start-date argument, it will return a zero. But lucky for us, Excel stores all of our entries as the serial dates, so the workaround for this is to just enter the cell location (B13) for your start date entry. If this is the first time you are seeing the NETWORKDAYS function, I hope you like it as much as I do!

Interesting fact about serial dates in Excel: The software knows to ignore leap years, with one exception in 1900—which wasn’t really a leap year. They made it like that on purpose, because Lotus 1-2-3 had that bug in its system and they wanted to make it completely compatible for us users. Nice example of “because that’s how we’ve always done it” that works to our advantage.