*This tutorial will teach you a few quick and easy ways to find out how many days are between two dates in Excel.*

Are you wondering how many days between two dates? Maybe, you need to know the number of days between today and some date in the past or future? Or, you just want to count working days between two dates? Whatever your problem is, one of the below examples will certainly provide a solution.

If you are looking for a quick answer, just supply the two dates in the corresponding cells, and our online calculator will show you how many days there are from date to date:

Curious to know the formula that has calculated your dates? It's as simple as `=B3-B2`

:)

Below you will find the detailed explanation on how this formula works and learn a few other methods to calculate days between dates in Excel.

The easiest way to calculate days between dates in Excel is by subtracting one date from another:

For example, to find out how many days are between dates in cells A2 and B2, you use this formula:

`=B2 - A2`

Where A2 is an earlier date, and B2 is a later date.

The result is an integer that represents no. of days between two dates:

As you probably know, Microsoft Excel stores dates as serial numbers starting on 1-Jan-1900, which is represented by the number 1. In this system, 2-Jan-1900 is stored as the number 2, 3-Jan-1900 as 3, and so on. So, when subtracting one date from another, you actually subtract the integers representing those dates.

In our example, the formula in C3, subtracts 43226 (the numeric value of 6-May-18) from 43309 (the numeric value of 28-Jul-18) and returns a result of 83 days.

The beauty of this method is that it works perfectly in all cases, no matter which date is older and which is newer. If you are subtracting a later date from an earlier date, like in row 5 in the screenshot above, the formula returns a difference as a negative number.

Another way to count days between dates in Excel is by using the DATEDIF function, which is specially designed to work out the date difference in various units, including days, months and years.

To get the number of days between 2 dates, you supply the start date in the first argument, end date in the second argument, and "d" unit in the last argument:

DATEDIF(start_date, end_date, "d")

In our example, the formula goes as follows:

`=DATEDIF(A2, B2, "d")`

Unlike the subtraction operation, a DATEDIF formula can only subtract an older date from a newer date, but not the other way round. If the start date is later than the end date, the formula throws a #NUM! error, like in row 5 in the screenshot below:

The users of Excel 2013 and Excel 2016 have one more amazingly simple way to calculate days between two dates - the DAYS function.

Please pay attention that compared to DATEDIF, a DAYS formula requires the arguments in the reverse order:

DAYS(end_date, start_date)

So, our formula takes the following shape:

`=DAYS(B2, A2)`

Like subtraction, it returns the difference as a positive or negative number, depending on whether the end date is greater or smaller than the start date:

In fact, calculating the number of days from or before a certain date is a particular case of "how many days between dates" math. For this, you can use any of the formulas discussed above and supply the TODAY function instead of one of the dates.

To calculate the number of days **since date**, i.e. between a past date and today:

TODAY() - *past_date*

To count the number of days **until date**, i.e. between a future date and today:

As an example, let's calculate the difference between today and an earlier date in A4:

`=TODAY() - A4`

And now, let's find out how many days are between today and a later date:

In situations when you need to get the number of working days between two dates, use the NETWORKDAYS function:

NETWORKDAYS(start_date, end_date, [holidays])

The first two arguments should already look familiar to you, and the third (optional) argument allows excluding a custom list of holidays from the day count.

To find out how many working days are between two dates in columns A and B, use this formula:

`=NETWORKDAYS(A2, B2)`

Optionally, you can enter your holiday list in some cells and tell the formula to leave out those days:

`=NETWORKDAYS(A2, B2, $A$9:$A$10)`

As the result, only business days between two dates are counted:

As you see, Microsoft Excel provides a handful of different ways to count days between dates. If you are not sure which formula to use, let our Date & Time Wizard do the how-many-days-between-two-dates calculation for you. Here's how:

- Select the cell in which you want to insert the formula.
- On the
*Ablebits Tools*tab, in the*Date & Time*group, click**Date & Time Wizard**:

- In the
*Date & Time Wizard*dialog window, switch to the**Difference**tab and do the following:- In the
*Date 1*box, enter the first date (start date) or a reference to the cell containing it. - In the
*Date 2*box, enter the second date (end date). - In the
*Difference in*box, select**D**.

The wizard immediately shows a formula preview in the cell and the result in the

*Difference in*box. - In the
- Click the
**Insert formula**button and have the formula inserted in the selected cell. Done!

A double-click on the fill handle, and the formula gets copied across the column:

To display the date difference in a slightly different way, you are free to choose any of the additional options:

*Show text labels*- the word "days" will appear along with the number, like shown in the screenshot below.*Do not show zero units*- if the date difference is 0 days, an empty string (blank cell) will be returned.*Negative result if Date 1 > Date 2*- the formula will return a negative number is the start date is later than the end date.

The screenshot below shows a couple of additional options in action:

This is how you calculate the number of days between dates in Excel. If you'd like to test our Date & Time Formula Wizard in your worksheets, you are welcome to download 14-day trial version of Ultimate Suite, which includes this as well as 60+ other time-saving tools for Excel. included. If you like the tools and decide to get a license, don't miss the 15% off coupon code that we provide exclusively to our blog readers: AB14-BlogSpo

To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook to How Many Days Between Dates.

I thank you for reading and hope to see you on our blog next week!

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 19 Responses to "How to calculate days between dates in Excel"

how to calculate times difference.

if start date and start time in different column and end date and end time are in different column than how to calculate.

Nitesh:

The formula is ((End Date + End Time)-(Start Date + Start Time))*24

Format each cell the way you usually do for each variable.

Format the result cell as General or Number.

Very informative and useful for me.

Thank you.

In testing, it appears the DATEDIF does not compute date values prior to Jan 1, 1900. What's the solution for computing date different (age, for example), if the birth date is in the late 1800s?

Matt:

According to Microsoft there is a VBA Macro you can use.

The address for it is:

https://support.microsoft.com/en-us/help/245104/how-to-calculate-ages-before-1-1-1900-in-excel

Hi my question is i want value against date 20/09/2018 Subtract 18/09/2018 in excel

Can you please tell how can I add a text in this for example tell me the formula for current date to my excel data date is greater than 5 days I need one text as abcd.. can you please help me in this formula

Could you please help me with the formula to work out how many days between dates, but if there is no start date (yet) to display 0 instead of 43388

Hello,

Please try the following formula:

=IF(ISBLANK(A1), 0, DATEDIF(A1,B1,"d"))

You can also learn about Excel date functions in Excel in this article on our blog.

Hope you’ll find this information helpful.

I would like to subtract Sundays from the networking days calculated

I need to know the day of the date in one coloumn

for Example 23/10/2018 what would be the Day?

For the 100 lines i will have the dates, i need to find the day

Hello, Prema.

Thank you for contacting us and for your question.

If you need to replace 23/10/2018 with the corresponding day of the week, you just need to select your cells, then press Ctrl+1 to open the Format Cells dialog. On the Number tab, select Custom from the Category list and type the following date format: dddd

You can learn more about date formats in Excel in this article on our blog.

Hope you’ll find this information helpful.

Hi

I am trying to total the number of days a certain job takes to do. However if the task is done in the same day how do I get excel to count this as 1 day?

Any feedback would be super - thank you

Danielle

Hi Danielle,

Assuming that your dates and in A and B columns, the formula below should work for you:

=IF(B1=A1,1,B1-A1)

Just enter this formula in C1 and then copy it along the column.

If you have any questions, don't hesitate to ask.

Hi,

I'm trying to do something similar to Danielle, but would like to calculate the number of days something is open, so I would need to use 3 dates to calculate this, date open, date closed and today.

Would anyone be able to help with the formula?

Thanks Vicky

Dear SIr,

Please share demo how to do like this because i trying so many time but i not get actual formula so i request to you share on video to how make formula this is very valuable formula.

Thanks & Best Regards

Mohammad Abid Quraishi

Hi,

I have entered entry and leaving dates from India for last 3 years, and have found the number of days in between. I want this for a range of dates, example in 2017, how many days was I in the country? Pls help, thanks.

Dear sir,

I want to calculate days from date to end date i.e if vacation start from 10 jan to 20 jan, by above formula it will show 10 days but atual it is 11 days. Can you explain please

I need a formula to calculate number of days between 2 dates. However, the number of days must be separated. Example: column A: 29/1/19 - 2/2/19, then column B = number of days for January, column C = number of day for February.