How Many Days Between X and Y

February 15, 2018 - by Bill Jelen

Counting the numbers of days between two dates in Excel seems deceptively simple. But if you simply subtract the earlier date from the later date, you might be mis-counting the number of days.

Let's take a very simple data from this week. Say that your team started a project on Tuesday and it is due at the end of the day on Thursday. If you simply subtract 2/13/2018 from 2/15/2018, Excel will tell you that there are 2 days. But that is not right. Count the days: Tuesday, Wednesday, Thursday. That is three days.

One solution is to use NETWORKDAYS as shown in B3 below. This function is different than a pure subtraction because it counts both the first day and the last day. In some cases, that will be better. (NETWORKDAYS can also ignore weekends and holidays, but that was discussed in detail in Podcast 2023 - Calculate Workdays.)

I am working on a series of 40 articles here at MrExcel. I started on Wednesday February 14 2018 and I want to end by March 31, 2018. I had always believed that there were 40 days between these two dates. But as you see below, the formula is calculating 46 days, not 40 days.

See, the number 40 is an important number to me. Why? Because =ROMAN(40) is XL and I spend my days writing about Excel. (And "XL" sounds like "Excel".) So, 40 is my favorite number. I've planned out 40 articles. But the calendar is giving me 46 days. I am not the only one with this dilemma. Throughout history, the number 40 keeps coming up. Noah battled rain for 40 days not 46 days. Casey Kasem would count down the Top 40, not the Top 46. Why is it that I believed there are 40 days between 2/14 and 3/31?

There have been various ways to schedule 40 days in this period (amazingly, Wikipedia describes six different ways that various people have calculated 40 days between these two dates!) One common method in the United States is to simply skip Sundays. (I am calling it the Methodist method, and I am choosing that method because the local Methodist church ladies make the best pie.) The modified formulas below come out to 40 articles in 40 days.

Here is the important lesson for today: check out that awesome secret syntax in B14. The tooltip for NETWORKDAYS.INTL and WORKDAY.INTL offers a choice where Sunday is considered the weekend. But what if you needed to skip Tuesdays and Thursdays? That is not an option. What you can do instead is to specify a 7-digit binary string as the third argument. Monday is the first bit, Sunday is the 7th bit. A 1 means to treat the day as a weekday. So, to skip Tuesdays and Thursdays, you would use "0101000" as the third argument.

Thoracic Thursday - my favorite heart-pounding features in Excel. I nearly had a coronary when I discovered this secret form of the third argument of NETWORKDAYS.INTL and WORKDAY.INTL.

Title Photo: The Digital Artist / pixabay