How to calculate days between dates in Excel

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.

Days between dates calculator

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.

How many days between dates calculation

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

Newer date - Older date

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:
Subtract one date from another to find out how many days are between dates.

How this formula works

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.

Calculate number of days between dates in Excel with DATEDIF

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:
DATEDIF formula to calculate the number of days between two dates in Excel

Note. DATEDIF is an undocumented function, meaning it is not present in the list of functions in Excel. To build a DATEDIF formula in your worksheet, you will have to type all the arguments manually.

Count days between dates with Excel DAYS function

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:
Count days between dates with the DAYS function.

How to calculate number of days between today and another 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:

Future_date - TODAY()

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

=TODAY() - A4
Calculate the number of days between a past date and today.

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

How to calculate working days between two dates in Excel

In situations when you need to get the number of days between two dates without weekends, 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)
Calculate working days between two dates in Excel.

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:
Business days between two dates are counted.

Tip. In case you need to handle custom weekends (e.g. weekends are Sunday and Monday or Sunday only), use the NETWORKDAYS.INTL function, which allows you to specify what days of the week should be considered weekends.

Find number of days between two dates with Date & Time Wizard

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:

  1. Select the cell in which you want to insert the formula.
  2. On the Ablebits Tools tab, in the Date & Time group, click Date & Time Wizard:
    Date & Time Formula Wizard for Excel
  3. 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.

  4. Click the Insert formula button and have the formula inserted in the selected cell. Done!
    Build a formula to calculate days between two dates in Excel.

A double-click on the fill handle, and the formula gets copied across the column:
A formula calculates how many days are between dates in two columns.

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:
Show the date difference with text labels

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 70+ other time-saving tools for Excel.

Available downloads

How Many Days Between Dates - examples (.xlsx file)

Table of contents

152 comments

  1. Hello,

    Is there a formula that can be used to calculate the total number of days for multiple date ranges in a table? I want to to avoid creating a new column (Total Days) and then adding the total number of days together to get the grand total.

    Sample Data:

    Start Date End Date Total Days
    11/9/2018 12/5/2018 26
    12/7/2018 12/25/2018 18
    12/10/2018 12/30/2018 20

    Grand Total days = 64

  2. I'm trying to workout number of days within a period and are within a period.
    for example Start Date 01/01/2022, End Date 31/06/2022, however i only want to know the days within 02/2022 within the intial period.

    Anyhelp from anyone

    • Hi!
      Sorry, it's not quite clear what you are trying to achieve. Do you want to get a list of days? Or do you need to find the sum for these days of February? Or count the number of days?

    • I really wanna know how to solve this with excel formula as well, thanks

  3. Hi Sir,

    I need to find the time period 00:00 to 08:00 hrs from two dates with time, for example 12/01/22 22:00 to 14/01/22 18:00. I would like to know how many times the period 00:00 to 08:00 occurred in this date range and specifically on which dates.

    Requesting your help.

  4. HOW TO DAYS CALCULATION DATE 25.06.21 TO 08.09.21

  5. I am trying to calculate the number of days between 2 dates, ensure I am including leap year thinking plus a cap at 3 years
    start date end date
    06/26/1989 07/19/2021 = 11711 days =DAYS(B4-A4)

    end date
    07/19/2021 - (minus) = new date
    how can i cap this from going back further than 3 years? 07/19/2018
    does this formula also include the extra day when a leap year is included?

  6. Need a formula to show the percentage reminding for a task if the start date in column A = 7/3/2021 and the due date in column B is 12/23/2021. Need to place % it in column D.

    Thanks

  7. I Have A question suppose in a cell I want subtraction of two dates and result comes Zero Day or 1 Day or 2 Days or Minus 1 Day or Minus 2 Days. How do the cells show Day or Days including the Numbers as mentioned?

  8. Formulla

    no. Of days between two dates when end date becomes first date.

  9. days between 1-4-2020 to 31-3-2021 is 365. when we use datedif , it comes 364

    • Hello!
      The calculation is correct, since the date 31-3-2021 means 31-3-2021 00:00:00. This day is not included in your date range.

      • Is there a way to include the start date and end date in the calculation?

  10. Hello. I opened a blank excel 2016 document and tried. None of your formula worked. I even tried making the cells formatted into Dates and they don't work

    I used

    =b2-a2

    =days

    None of them...

  11. How to calculate yeasr, months, days, hours & minutes with total number of hours ?

  12. How to calculate difference between two dates i.e. for same day 'Ok' and for last date 'delay'.

  13. Dear Sir,

    I am ins search with a formula which can be used for - difference or Addition between 2 dates with outocme in Date only

  14. Thanks a lot for your article! now I know how easy It is.

  15. How do I calculate days between two dates in Excel(in solar calendar)

  16. Sir I want to Calculate number of days between start date & time to end date & time i.e 20-11-2020 07:00 am to 21-11-2020 07:00 am 1 day if date 21-11-2020 07:01 am then 2 days it will show if DATE IS 20-11-2020 time is any time after 07:00 am to 21-11-2020 07:00 am it calculate 1 days after 07:00 am it calculate 2 days automatically can you help me ?

    • Hello!
      What formula are you using? What format is set in a cell with a formula? Remember that dates and times are recorded in Excel as numbers.

  17. In excel 2007 database.
    I have current date in column A, there are also dates in columns AH, AX, BI and BS.
    It would be very useful if I can replace date with days between A and each of the other 4. so number of days between A and AH, A and AX, A and BI, and A and BS. Since there are in excess of 30000 rows, it would be useful to automatically calculate the days for each row with a single formula!
    Found your articles very informative and hope you can assist with this rather strange request.
    Many thanks in anticipation,
    Stay safe,
    Regards,
    Doug

    • Hello!
      Unfortunately, you can only replace the date in a cell with the number of days using a VBA macro. If a cell contains a date, then it is no longer possible to write a formula to it.
      If there is anything else I can help you with, please let me know.

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

    =TODAY() - A4

    From the above example I am trying to calculate the number of working days.
    The date Column1
    21/10/2020 In this column I would like to display the number of working days from 24/10/2020 till today

    Please help.

  19. I would like a formula to calculate 21 working days from any given date, can you help please

  20. V4-INT((V4-WEEKDAY(K4)+1)/7)-1

    i have come across this formula, i just need to understand how exactly it works.

    V4= column containing the Difference between two dates.
    K4= column containing different dates and times.

    kindly help.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)