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

154 comments

  1. If the starting date and ending date happen to be on the same day, then the formula returns a value of 1 to indicate that the length of stay is 1 day using
    dd-mm-yyyy hh:mm.

    1. Hi! If you’re calculating the number of days between dates that share the same day but have different times, the NETWORKDAYS function returns 1. All the other formulas will return either 0 or a decimal number that is less than 1.

  2. Good morning, is there a way for me to easily paste this formula for the rows following? or do I manually have to type the changing letters in the formula?

  3. how can i calculate number of days between 01-07-2024 and 31-07-2024 both days inclusive and secondly it should also caclulate number of days as of today if there is no end date in a same formula.

    1. Case 1 01-Mar-2024 31-Mar-2024 31 Days Number of Days Formula inclusive of both dates & if there is no end date than it should calculate days as of today
      Case 2 01-Mar-2024 130 Days Number of Days Formula inclusive of both dates & if there is no end date than it should calculate days as of today

      Suppose today is 08-July-2024

      1. Hi! I have written many times on this blog that a date without a time means 00:00:00. Therefore, an end date without a time can never be included in counting the difference in days.
        From 01-Mar-2024 to 31-Mar-2024 is 30 days. If you want to include both dates in the day count, either include the time in the end date or add a day to the result.
        If no end date is specified, use the IF function to calculate by condition. You can find the examples and detailed instructions here: IF function in Excel: formula examples for text, numbers, dates, blanks. The formula might look like this:

        =IF(A2="",NOW()-A1,A2-A1)

        To get the current date and time, the NOW function is used.

  4. I Want to check, i have do a file:
    PLACE DATE:29/01/2024
    MATURE DATE: 29/04/2024
    MY FORMULA AS BELOW:-
    =IF($I8<TODAY(),"MATURE",I8-TODAY())
    RESULT IS "21 DAYS"
    I think the result was wrong, can you help me check whether my formula was wrong?
    Thank in advance for your help!

      1. you just ignore I8, I8 is the cell the data i key in

  5. Good Day Alexander

    I need to get a formula to calculate number of days between two dates and also a deduction of the number as the day go by.
    example: If difference between two dates is 60 days, let the 60 reduce in each new days until it is zero (0).

    i have solve the problem of getting the number of days in between two days but need help on how to subtract the 60days as the days goes by.
    #. 16/11/2023 - 05/01/2024 = 60 days

    #. Day 1 = Let the 60days be deducted by 1
    #. Day 2 = let the 59 days keep reducing
    #. Day 3 = .....
    #. Until the 60 days remains 0 day

    Please i need your assistance to get this sorted out

    1. Hi! If I understand the question correctly, you can from the end date subtract the current date, which can be determined using the TODAY function. For example,

      =B1-TODAY()

      If this does not help, explain the problem in more detail.

  6. How can I calculate the dates in one month vs. the dates in another month in a date range. Ex. Date Range 08/16/23-09/18/23. I need to be able to split the costs associated with these dates into their respective months.

  7. How do I calculate the amount of days from 8am August first 2007 to 3pm Oct 1 2023

  8. how to calculate days between two dates i.e. suppose 16/7/2023 is the end date of fiscal year and 14/04/2023 is the start date. Further, if we cross the end date, the number of days will be of color red? how will we do that

  9. HI and Thank you for sharing this

    It is work fine but i faced an issue

    when I calculate the days between 23/07/2023 and 31/07/2023 will return 6 but the correct result is 7 days,

    note: i faced in different dates as well

    can you please help me in this.

    Thanks

      1. No the correct result is 7 if you count it from the calendar (normal count). with out weekend days

        but if I used your formal the result will show me 6 days and that not correct.
        but the same formal I used in different days [03 July 2023 t0 13 july 2023] without weekend days the count of days will be 9, and that correct

        but why in the first one not correct, and i am using the same formal.

        I hope you get my point.

        Thanks

        1. Your previous question didn't say anything about weekends. Also, I can't know what formula you use and what days are counted as weekends. However, we have written many times here that a date without a time is implied at the beginning of the day. For example, your dates are 23.07.23 00:00:00 to 31.07.23 00:00:00.

  10. HI, what is the formula to Return the last year of the given dates?
    E.g.
    19 May 2022 to 31 Dec 2022
    19 Jan 2021 to 31 Dec 2021

  11. Hello,
    The question I have is related to dates in Excel since I am trying to find the difference between the two dates. Here are some examples: Concerning item A, everything is clear, as I used datedif, days, and the normal B1-A1 formula. Item B has an error because the days and months are different from Item A. Item B will be changed like item A, so I need to modify with formula 12/05/2026 to 05/12/2026 using a formula. Could you please assist in this issue?

    Start End Diff.
    A) 04/24/2026 05/26/2026 32
    B) 12/05/2026 14/08/2026 -

    Thanks

  12. Need some help with this concern... thank you in advance

    Leave is between Jan 01-2023 Until Jan 10-2023

    there is a weekly report stating how many leaves done in a weekly basis

    Week start Jan 02 - result should be 5 days not counting 2 days as literally it is an OFF
    Week start Jan 09 - result should be 2 days

    above item is just the rationale....

    the real data is

    Employee 1 - Leave between Dec 30 2022 until June 1 2023
    Employee 2 - Leave between Jan 1, 2023 until Jan 10, 2023
    Employee 3 - Leave between Jan 6, 2023 until Jan 11, 2023

    Result should be
    WS Jan 02 - 13 days
    WS Jan 09 - 10 days

    note cap days is 5 days only

    Wish you could help me this one

  13. Greetings,

    I need help on excel formula to calculating penalty below.

    There is penalty of 1% of the tax due for failure to file a return on time plus an additional 1%
    for each thirty days the return is late. The minimum penalty for late payment is $5.00

    Thanks

  14. In case start date and end date is same then how will we calculate the day

  15. Hello! First of all, thank you. As an Excel newbie, I find all of your tutorials very helpful. If you would be so kind as to let me know if there is a formula for my problem. My boss wants the total absences for each month totaled. However, the way the workbook is set out is "start date" and "end date". My problem is some absences' end date crossed another month.

    Type Start Date End DateTotal Days
    Sick Leave Apr-12 Apr-13 2
    Sick Leave Apr-30 May-02 3

    1. Hello!
      Add another column D with the formula

      =MIN(C1,EOMONTH(B1,0))

      The EOMONTH function will determine the last day of the month.
      Find the difference in days between columns D and A as described in the article above.
      I hope it’ll be helpful.

  16. Hello,

    I apologise if this has already been asked.
    I am wanting to work out the number of days between dates and clearly this is the right place.
    I have followed the simple suggestion of minus one date cell from the other and this, for example, calculates 1 day between 27 and 28 March 2022.
    However, with my job, I class this as 2 days. I start work on 27 March and leave on the 28 and charge 2 full days rate.
    How do I ask Excel to see this as 2 days.
    Another example would be 09 to 24 January 2023 where Excel is showing 15 days when I need it to calculate 16 days

    Please help :-)

  17. How do I calculate the number of days between 2 dates if the dates are in this number format: 11/12/2021 and 04/01/2022? It seems Excel can only do it if the format is either like this: 2021/12/11 or 11-Dec-2021

  18. Hi there,

    I need help calculating the difference between two dates. I need to find out how many days a patient has been in our facility based on the entry /discharge dates within the month of November 2021 for example. Below are the entry/discharge dates and the number of days I need to obtain.Where discharge day is blank means the pacient is still in the facility so I need to use the month end date.
    Any help/comment is much appreciated. Many thanks.

    Beginning period End period
    1/11/2021 30/11/2021

    Entry date Discharge date No of days
    1/10/2021 30
    16/11/2021 21/12/2021 15
    3/11/2021 26/11/2021 23
    1/11/2021 30
    28/09/2021 15/11/2021 14

    1. Hi!
      To get the last day of the month uses the EOMONTH function.
      Please try the following formula:

      =IF(B1="",EOMONTH(A1,0)-A1,B1-A1)

      However, I do not really understand how between November 16 and December 21 you get 15 days.

  19. Hi,

    Now I have another Issue. That is, I need to find the days after the days crossed (for example: I entered one employee passport date of expiry 15/05/2020 and after the expiry date the days are not counting)

    can you please clarify this also...

    And your formula works, but I need to find also the days after expiry (i.e days with - )

    1. Hi!
      Please try the following formulas:

      =IF(A1 < TODAY(),TODAY()-A1,"")
      or
      =IF(A1 < TODAY(),TODAY()-A1,A1-TODAY())

      Depending on what result you want to get.

      1. Hi

        I have tried your formula but its not what I ask. So i have shared you my sample excel with you. If possible please clarify.

        Actually I need all in one cell like If I enter past date, todays date or upcoming dates. I need to get result.

        =IF(A1 < TODAY(),TODAY()-A1,A1-TODAY()) This formula it is not showing the past dates result in (Minus)

        For Example

        Passport Expiry - 25/08/2019
        The result coming - 975 days (by using your formula)

        But I need result - (-975 days)

        if Passport Expiry - 25/08/2022
        The result - 121 days (this is ok)

        So I need the past dates result in minus (-)

        Hope you understand my question. Please clarify

        Thanks

          1. Hi,

            Thanks for your help. It working now but still I can see the cell with (-44678). But its ok... Thanks again

        2. Hi,

          Sorry the below result is also not correct.

          if Passport Expiry - 25/08/2022
          The result - 121 days (this is also not correct)

          Please clarify

          Thanks

  20. Hi,

    I would like to calculate the remaining days with the current date (TODAYS DATE). So I use formula (=upcoming date - TODAY()). I get the result. But if I am coping same formula to the other cells which is "upcoming date cell is empty" the result showing "-44673" . But I need the cell to be empty or 0 instead of showing this "-44673". Is there any solution Please help me out.

    Thanks

      1. Hi,

        Now I have another Issue. That is, I need to find the days after the days crossed (for example: I entered one employee passport date of expiry 15/05/2020 and after the expiry date the days are not counting)

        can you please clarify this also...

        And your formula works, but I need to find also the days after expiry (i.e days with - )

      2. Hi,

        Thanks for your reply and Yes I got my answer... Its perfect. Thanks again for help me out...

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 :)