How many days between two dates? In Excel, the answer is a simple subtraction, but you need to know which days you are counting. The time it took a customer to pay an invoice and the number of days in a holiday period are two different questions, and they don’t give the same result.
Try it right now
Ten invoices, an issue date and a payment date: how long did each customer take to pay? Work it out in 6 steps, free and corrected as you type:
Subtract the two dates
Excel stores every date as a number: 1 for 1 January 1900, then one more for each day. So the difference between two dates is just one date minus the other.
=B2-B1
Between 23 October and 15 November 2023, the result is 23. That is the number of days that have passed: a payment time, a delivery delay, the nights spent in a hotel. This is what the exercise above uses.
💡 If the result shows as a date instead of a number, change the cell format to General.
Add 1 when both days count
Now count the days on a calendar from 23 October to 15 November: there are 24, not 23. The subtraction leaves out the first day. When both the first and the last day are part of the period (days of holiday, days worked on a project, days of a training course), add 1:
=B2-B1+1
To count full weeks, divide by 7 and keep the whole part with INT: =INT((B2-B1)/7) returns 3.
Count only the working days
To leave out Saturdays and Sundays, use NETWORKDAYS. The first argument is the start date, the second the end date:
=NETWORKDAYS(B1,B2)
The result is 18. NETWORKDAYS already counts the first and the last day, so there is no need to add 1.
Remove the public holidays too
Type the holidays in a range of cells, then select that range as the third argument:
=NETWORKDAYS(B1,B2,A8:A11)
With 4 holidays falling on weekdays, the result drops to 14. A holiday that falls on a weekend changes nothing, because that day was already excluded.
If your weekend is not Saturday and Sunday, use NETWORKDAYS.INTL. Its third argument sets the weekend: for example, 7 for Friday and Saturday.
=NETWORKDAYS.INTL(B1,B2,7)
