Part 1 – Determine Number of Days Between Two Dates
An easy, and common strategy to determine the number of days between two dates is to subtract the startdate from the enddate.
Let’s create a formula that takes the enddate (B5) and deducts the startdate (B4). This way we can get the number of days that are between these two dates.
Now remember, dates are just numbers in Excel. If we switch the number formatting from Date to General, we see the numbers associated with these dates.
Dates start counting from January 1, 1900. This means that December 3, 2018 is the 43,437th day since January 1, 1900.
If we change cell B4 to 1 and restore the formatting to a Date style, we see January 1, 1900.
Featured Course
Visually Effective Excel Dashboards
Part 2 – Determine Number of Days Between Two Dates – Less Fridays
Another formula that calculates the number of days between two dates and excludes certain days from our calculation, is the NETWORKDAYS formula. NETWORKDAYS originally assumed that the days it should exclude are the weekends, so the original NETWORKDAYS formula just requires the startdate and the enddate; it automatically excludes weekends, and it assumes that weekends are Saturdays and Sundays.
Not all countries observe their weekends on Saturday and Sunday (many Middle East countries observe Friday/Saturday as their weekend), and not everyone takes 2-day weekends. Because of this, beginning in Excel 2010 we were provided with an updated version of the formula called NETWORKDAYS.INTL (international). With NETWORKDAYS.INTL we can define the type of weekend that we have in our country as well as a 1-day or 2-day (sequential) weekend.
For our startdate we will use 12/3/2018 and our enddate will use 12/22/2018. For our weekend we can pick Saturday, Sunday or we can scroll down and pick “Friday Only”.
If we wanted to exclude Monday’s only, we would select 12, but in this case, we wish to exclude all Fridays, so we’re going select 16. What this does is counts the number of days between the two dates and while excluding Fridays.
We can use the information from these two formulas to calculate how many Friday’s we have, but there’s one thing we must be careful with and that’s what these days include and exclude.
When we get the difference between these two days, are we including the startdate and the enddate or is one of them, or both, excluded? That is something we must take care of when deducting one from the other.
To check that out, let’s restrict our date range to a smaller range so we can quickly look at the calendar and find out what’s included and excluded.
Let’s switch the startdate to the 3rd (Monday) and the enddate to the 6th (Thursday). There are no Friday included, but look at our results.
We get different answers. The formula in cell B7 states that there are 3 days between the startdate and enddate? One of these days is not included. The formula in cell B7 is excluding the startdate, whereas the NETWORKDAYS.INTL in cell B8 includes both the startdate and the enddate.
To be able to compare them to one another, we need to make an adjustment to the formula in cell B7. We’re going to add a 1 to result. This will ensure we are including the startdate and enddate.
Part 3 – Determine the Difference
Now we have the necessary information to calculate how many Fridays are between these two days.
We have how many days are in between these two dates (B7); we have how many days are between these two dates but excluding Fridays (B8). All we must do is subtract one number (B7) from the other (B8).
If we switch the enddate to December 8th, we get 1; from the 3rd to the 8th there’s 1 Friday.
If we switch the enddate to December 22nd, we get 3.
Part 4 – Perform the Logic in a Single Formula
Let’s consolidate the logic into a single formula. The logic is as follows:
- Subtract the startdate from the enddate
- Add 1 to compensate for the missing startdate
- Deduct the number of days without Fridays
We get a date because Excel thinks we want a date. We don’t want a date, we just want a number. Let’s switch the formatting style of cell B1 to the General format.
Testing a Larger Date Range
Let’s look at a bigger timeline. We will switch cell B5 to January 31, 2019; we get 8 Fridays between the two dates.
The NETWORKDAYS.INTL formula, along with other date formulas, are covered in my FREE eBook called “Top 10 Essential Excel Formulas to Work Smarter”. If you are interested in this FREE eBook, click the link below.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
