Want to know how many more days until your birthday? Or the age difference between you and a friend?
Or on a more serious note, if you need to know the number of days between two project milestones, there is an easy way to calculate this in MS Excel.
The good news is that there are several ways to calculate the number of days between two dates in MS Excel.
4 Methods to calculate the number of days between two dates in MS Excel:
- Subtract the dates
- Use the DATEDIF function
- Use the DAYS function
- Calculate the number of working days using the NETWORKDAYS function
Method #1: Subtract the Dates
This is one of the simplest methods to calculate the number of days between two dates. All you have to do is subtract the end date from the start date to get the number of days in between them.
If you subtract the start date from the end date, or if the start date is more recent than the end date, the formula won’t throw an error, but it’ll give you an answer in negative.
Step #1: Open your Excel workbook
If you already have an existing workbook with the start and end dates, open that.
Otherwise, create a new Excel workbook and enter the dates.
Step #2: Select the results cell
Now, select a cell by clicking on it.
You will enter the formula in this cell, and this is where it will display the number of days in between the two dates.
In our example, we select cell C2 as this is where we want to see the results.
You can confirm this using the Name box, which is to the top and left of your spreadsheet.
Step #3: Enter the formula
Now, enter the formula, which will subtract the end date from the start date.
=End date – Start date
In this instance, the start date is in cell A2 and the end date is in cell B2, so the formula would be:
=B2-A2
You can either type in the formula using the cell names on your keyboard as above, or:
You type = on your keyboard, then click with your mouse on the cell B2, type – on your keyboard, and then click with your mouse on cell A2.
Step #4: Press Enter
Once you’ve typed in the formula, press Enter.
You’ll now be able to see the number of days between the two dates.
Method #2: Use the DATEDIF Function
MS Excel also has an in-built function called DATEDIF. This calculates the number of days between two dates. Not only days, but with this function, you can also calculate the number of weeks, months, and years as well.
However, you can only subtract the start date from the end date, and the start date must be older than the end date. If you do it the wrong way around, the function will throw a #NUM error.
Step #1: Open your Excel workbook
Create a new Excel worksheet and enter the dates.
If you already have one with dates, open the existing workbook.
Step #2: Select the results cell
Now, select a cell by clicking on it.
You will enter the formula in this cell, and this is where it will display the number of days in between the two dates.
In our example, we select cell C3 as this is where we want to see the results.
You can confirm this using the Name box, which is to the top and left of your spreadsheet.
Step #3: Enter the DATEDIF function
The syntax for the formula is =DATEDIF(start_date, end_date, “d”).
The third parameter, d, represents days. It calculates the number of days between the start date and the end date.
To calculate the number of months between the two dates, you can change the third parameter to m. For years, change the third parameter to y.
In our example, the formula would be =DATEDIF(A3, B3, “d”).
Don’t forget the “ “ !
Step #4: Press Enter
Once you’ve entered the function, press Enter on your keyboard.
You can now see the results in the chosen cell.
Method #3: Use the DAYS Function
You can also use the DAYS function to get the number of days between two dates.
If you subtract the start date from the end date, or if the start date is more recent than the end date, the formula won’t throw an error, but it’ll give you an answer in negative.
Step #1: Open your Excel workbook
Create a new Excel worksheet and enter the dates.
If you already have one with dates, open the existing workbook.
Step #2: Select the results cell
Now, select a cell by clicking on it.
You will enter the formula in this cell, and this is where it will display the number of days in between the two dates.
In our example, we select cell C4 as this is where we want to see the results.
You can confirm this using the Name box, which is to the top and left of your spreadsheet.
Step #3: Enter the DAYS function
The syntax for the DAYS formula is =DAYS(end_date, start_date).
In our example, the function would be =DAYS(B4,A4)
You can either type the whole formula, or you can:
Type =DAYS( on your keyboard, click on the B4 cell with your mouse, add a , on your keyboard, click on the A4 cell with your mouse, and add a closing bracket ) on your keyboard.
Step #4: Press Enter
After typing the function, press Enter to see the number of days.
Method #4: Calculate the Number of Working Days between Two Dates Using the NETWORKDAYS Function
If you want to calculate only working days, you can use the NETWORKDAYS function. This function excludes weekends and any other specified dates.
If you subtract the start date from the end date, or if the start date is more recent than the end date, the formula won’t throw an error, but it’ll give you an answer in negative.
Step #1: Open your Excel worksheet
Create a new Excel worksheet and enter the dates.
If you already have one with dates, open the existing workbook.
Step #2: Select the results cell
Now, select a cell by clicking on it.
You will enter the formula in this cell, and this is where it will display the number of days in between the two dates.
In our example, we select cell C5 as this is where we want to see the results.
You can confirm this using the Name box, which is to the top and left of your spreadsheet.
Step #3: Enter the NETWORKDAYS function
The syntax is =NETWORKDAYS(start_date,end_date,[holidays]).
The third parameter is optional.
In our example, the function would be =NETWORKDAYS(A5,B5).
You can either type the whole formula on your keyboard or you can:
Type =NETWORKDAYS( then click with your mouse on the cell A5, type a comma , on the keyboard, click with your mouse on B5 and add a closing bracket ) at the end.
Step #4: Press Enter
Press Enter on your keyboard to see the function in action.
You can see that the number of working days is 23. The function excluded all weekends.
Conclusion
No more struggling to count the number of days between two dates! You now know four easy methods to calculate the number of days between any two dates.