Excel offers several ways including multiple functions to calculate the number of days between two dates in Excel.
In this tutorial, we will learn a couple of methods to find the difference between two dates. Also includes situations where we need to know the days between today and another date or we like to count business days or working days, or we like to know how many weekends occur between two dates.
In this tutorial:
Count the Number of Days Between Two Dates in Excel
There are two simple ways to calculate the days between two dates:
- Subtract the start date from the end date
- Use Excel functions like DAYS or DATEDIF
Here is the sample data for this tutorial:
Subtract two dates in Excel
The simplest way to count the number of days between the two dates is by subtracting one date from the other.
With the end date in column B and the start date in column A, put the following Excel formula in cell C2 and hit Enter key:
=B2-A2
This will calculate the total days between the two selected dates.
For the rest of the data range, simply double-click the fill handle.
Using the DAYS Function
Excel DAYS function is meant to count total number of days between the start and the end dates.
The Excel DAYS function returns the number of days between start and end date. Once we specify start date and end date, DAYS function will give us the total number of days between specified dates.
Returning to our example, we have “Start date” data in column A and “End date” in column B. Put the following formula in cell C2 and press Enter key:
=DAYS(B2,A2)
Afterwards drag the fill handle to calculate days for each set of dates.
Using the DATEDIF Function
Excel DATEDIF is another flexible function that can return the number of days between two dates. DATEDIF is short for “date difference” or “date differential”. In other words, this function calculates the difference between two given dates.
Unlike the Excel DAYS function which can only count the difference in days, DATEDIF can calculate the time difference in terms of months or years, etc. as well.
Note: DATEDIF is only a compatibility function. Meaning it’s not part of the Excel list of functions and therefore no help tooltip will be available for this function while typing. So we need to remember the syntax and the arguments of this function ourself.
The format or syntax of the DATEDIF function is following:
=DATEDIF(start_date,end_date,unit)
The first two arguments i.e. start_date and end_date are self-explanatory.
Unit means the unit of measurement you like to use to measure time or in other words the type of information you require. It takes the following input:
| Unit | Difference calculated in terms of: |
|---|---|
| D | Days |
| M | Months |
| Y | Years |
| MD | only days. The months and year are ignored |
| YM | only months. The days and years are ignored |
| YD | Only years. The months and days are ignored. |
Resuming with our example, following formula will get the total number of days between two dates using DATEDIF function:
=DATEDIF(A2,B2,"D")
Calculate number of months between two dates
Similarly, if we like to calculate the number of months completed between two dates, then the formula will be:
=DATEDIF(A2,B2,"M")
Following is the result for our example data:
Calculate the total number of Working Days Between Two Dates in Excel
To calculate the number of working days between two dates instead of the total number of days, Excel has two functions:
- NETWORKDAYS – used when Saturday and Sunday are weekends
- NETWORKDAYS.INTL – used if weekend days are other than Saturday and Sunday.
To calculate number of days without weekends in excel, these functions are super easy to use.
NETWORKDAYS
Returning to our example, the following formula will get us total workdays for a set of dates considering Saturday and Sunday as weekend days:
=NETWORKDAYS(A2,B2)
To calculate number of days excluding weekends and holidays in excel, we simply need to specify data range containing holiday dates in [holiday] argument of the function.
With holidays mentioned in column E from E2 to E16, following is the formula to calculate number of days excluding weekends and holidays:
=NETWORKDAYS(A2,B2,$E$2:$E$16)
As a result, every weekend and holiday is ignored to get the total number of workdays between two dates.
Again, for NETWORKDAY function, Saturday and Sunday, both days are weekends and are ignored in weekday calculation. However, this is not always the case.
For example, in some countries only Sunday is the weekend. Similarly, in some countries Friday and Saturday are weekends. For such cases, Excel has NETWORKDAY.INTL function where INTL = International.
NETWORKDAYS.INTL
NETWORKDAY.INTL has an additional argument that let you select the weekend day or days. That way you can calculate the workdays correctly based on the local calendar.
Following is the syntax of this formula:
=NETWORKDAY.INTL(start_date,end_date,[weekend],[holidays])
start_date and end_date arguments are self explanatory.
[weekend] is an optional argument. If left blank, Saturday and Sunday are considered weekends. A certain value is used to specify the relevant weekend.
The following table lists all the values that can be used in the argument and the weekends each indicates:
| Weekend number | Weekend days |
| 1 or omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
Coming back to our example, if only Sunday is the weekend day then the formula is following:
=NETWORKDAYS.INTL(A2,B2,11)
And to account for holidays as well we simply need to specify the range containing holiday dates. In our case it is E2:E9. The formula is following:
=NETWORKDAYS.INTL(A2,B2,11,$E$2:$E$9)
Calculate Number of Weekends Between Two Dates in Excel
To calculate the total number of weekends between two dates, we use a combination of DAYS and NETWORKDAYS function.
DAYS function will give us total number of days, whereas NETWORKDAYS will give us the total days excluding weekends.
Subtracting the results of these two functions will give us total number of weekends days between two dates.
Continuing with our example, the formula will be:
=DAYS(B2,A2)+1-NETWORKDAYS(A2,B2)
Again, DAYS function is giving us total number of days between two dates. We made “+1” because we want to include the starting day as well in the calculation. NETWORKDAYS results in total number of workdays assuming Saturday and Sunday as weekend.
Here is the result of our example:
If Saturday and Sundar are not the weekends for your local calendar, then we can replace the NETWOKDAYS function with NETWORKDAYS.INTL function and select the correct weekend for our calculation of weekends days between two dates.
Suppose only Sunday is the weekend then our formula will become:
=DAYS(B2,A2)+1-NETWORKDAYS.INTL(A2,B2,11)
And here is the result in this case:
Calculate Number of Off days Between Two Dates in Excel
To find the total number of off days between two dates, we will use the same DAYS and NETWORKDAYS combination with holidays mentioned in NETWORKDAYS function. That way, all the non-working days will be counted that includes weekends and holidays.
With holidays data mentioned in E2:E9 range and Saturday+Sunday assumed as weekend, our formula for our example will be:
=DAYS(B2,A2)+1-NETWORKDAYS(A2,B2,$E$2:$E$9)
And if only Sunday is the weekend then formula to count non-working days in Excel including holidays is:
=DAYS(B2,A2)+1-NETWORKDAYS.INTL(A2,B2,11,$E$2:$E$9)
Count day of the week Between Two Dates
To count the number of Mondays between two dates or if we like to know how many Fridays between two dates are there, we can use NETWORKDAY.INTL function with a lesser known trick.
For instance, I want to find the number of Mondays between dates in our example data. We use the following formula:
=NETWORKDAYS.INTL(A2,B2,"0111111")
Instead of using tha premade values to specifiy weekend, we inserted a combination of 0 and 1s in quotes.
First day being Monday and last day being Sunday, 0 means workday and 1 means non-workday.
As we have only the first day i.e. Monday as the workday, the above formula will count only Mondays between two dates.
Similarly following Excel formula will count total number of Fridays between dates:
=NETWORKDAYS.INTL(A2,B2,"1111011")
Calculate Number of Workdays in a Part-time Job
To count the total number of working days in a part-time job setting, we can use the same trick used in counting a weekday above.
Under part-time job, employee can have a two day three day or four day work week.
Similarly, a company may have a three day weekends policy. In such cases NETWORKDAY.INTL let us count the workdays easily.
Using our example data, if an employee is working only on Mondays and Fridays then formula is:
=NETWORKDAYS.INTL(A7,B7,"0111011")
Similarly if a company has three day weekend policy where Friday, Saturday and Sunday are weekends then use the following formula to count the workdays:
=NETWORKDAYS.INTL(A2,B2,"0000111")
Now using the same technique, you can count number of any weekday or weekdays in Excel. Just remember 0 means workday and 1 indicates a day off.
Hope you find this tutorial helpful.
Don’t forget to check out the following Excel tutorials:
