Comments on: How to calculate days between dates in Excel

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

Comments page 3. Total comments: 154

  1. 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.

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

  3. 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.

  4. I want a formulae to count every month as 30 days.

    1. I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  5. I have four columns with dates. First column in date of receipt(7/1/20), second column is date of 1st response (7/5/20), third column is IF a 2nd response is needed and fourth column is IF a third response is needed. I need a column that totals # of days from date of receipt until last response (whether 1, 2, or 3). How do I build a formula for that?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =MAX(A1:D1)-A1

      Hope this is what you need.

  6. I have 5 customer transactions, each on different days, displayed on different rows, there is a common customer ID - i want to understand how many days from the earliest transaction date to the most recent transaction date. ie. Calculating customer lifetime in days. Help? Thanks!

    1. Note - i could have 20k rows of customer data so cannot manually take dates to calculate. Needs to be something like IF Customer ID = "Customer ID", calculate days between Newer Date and Older Date...

      1. Hello Kathryn!
        Unfortunately, without seeing your data it is impossible to give you advice.

        I'm sorry, it is not very clear what result you want to get. Could you please describe your task in more detail and send us a small sample workbook with the source data and expected result to support@ablebits.com? Please shorten your tables to 10-20 rows/columns and include the link to your blog comment.

        We'll look into your task and try to help.

  7. Hi, Hope someone could help.
    I have 2 days the same date.
    The difference of days will be Zero, 0.
    What I want is to days to todate.
    For instance,
    A1 B1 C1
    3/29/2012 3/29/2012 A1-B1 + days till today(for instance 26 July 2020)
    Hope someone could help.
    Thank you,
    Felicia

    1. Hello Felicia!
      I don't quite understand what your problem is. Read the section "How many days between dates calculation" above. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you.

  8. date + days = count date
    02/06/20 + 5 = 06/06/20

    1. Hello!
      Your date 02/06/20 in its entirety looks like this: 02/06/20 00:00:00. Therefore, when you add 5 days, one of them is 02/06/2020. And then add another 4 days. We get 06/06/20

    2. plz help for how to cakculate ??

  9. Hi There,
    My question is if I want to know the days gap between 2 dates, but it should not calculate the first day. From the the second day the counting should begin.

    Here is a query -
    Start Date 10-March-2020
    End Date 15-March-2020
    Leaving Date 11-March-2020, counting should begin from 12-March-2020.
    Answer should be 4.

  10. hi
    wow networkdays is properly working.
    thank you

  11. i have to do this =b2-a2 formula for over a 1000 lines. how do i do this for the entire column?

  12. Hi there,
    It appears that DATEIF doesn't work in excel online - is there an alternate formula
    Cheers

  13. i have a query:
    i want to calculate the no of days starting from say 1st Jan, 12 AM midnight( start of day) to 5th Jan, 12 AM midnight (end of day).
    this should give me 5 days but the excel formula gives me 4 days
    how do i calculate that?

  14. What's the formula if I want to know the age of an invoice as of today?
    A1 - 9-dec-2019 (invoice date)
    B1 - 9-Jan-2020 (invoice due date)
    c1 - age as of Jan 30 (date today)
    Thanks for the help in advance

  15. Positive thinking leads to positive life, and negative thinking leads to negative life

  16. Hi
    I need to enter a third date into the argument. I am counting the number of days elapsed between a date in the past (say in cell A1) and 'TODAY' (say the formula is in Cell A2) , but I then need to add in a completion date (say in cell A3) and need the number of days to stop counting once the completion date is entered. How do I tell the formula to stop counting when that completion date is entered? Oh, I also need to tell the formula that if A1 is blank, leave A2 blank. So at the moment my formula would look like this:
    =IF(ISBLANK(A1),"",(TODAY()-A1))

  17. If the date format in your excel sheet does not reflect what is displayed on your windows taskbar, then this may not work for you. In order to fix this you can change your Regional format data with the following instructions:
    Right-click on time and choose "adjust date/time"
    Under related settings choose "Date, time, & regional formatting"
    Under Regional format data choose "Change data formats"
    Select the short date format that works for you
    Bask in the glory of solving this mystery that plagued me for longer than I care to mention.

  18. This counter is not that good... He counts first and last day. And then it is not correct. For example I need to count 90 days from 17 nov. Last day I get 15 febr. But it is not right - then I have 91 day!!! He should not count the last day...

  19. I need to include start date as well end date so what to do. I just did A2-B2+1. any other formula

  20. Hello,,
    Good Morning.
    I need Deviation in days between Actual date and Desired date as per Planner,,
    Example..
    Cell A1 is last calibration date (22 August 2019)
    Cell B1 is Next Calibration date (22 Sept 2019) calibration validity is 1 month.
    Cell A 10 is calibration done date (27 sept 2019)
    how to calculate the calibration deviation in the planner,,

  21. I need to prepare a summary of different workers. how i calculate in a single formula that how many workers we have staff cadre and are regular and contract and how many staff are regular and contract.

  22. I need help with calculating the number of holidays in a per month.
    These are the holidays for 2019:
    1-Jan-19
    9-Apr-19
    18-Apr-19
    1-May-19
    5-Jun-19
    These are the holiday for 2020:
    1-Jan-20
    9-Apr-20
    10-Apr-20
    1-May-20
    24-May-20

  23. Please i need help,find the number of days between two dates

  24. I want formula for counting days between two dates. In case of from date and to date are same so here the number of days is 1. But all above formula shows 0.

    1. =IF(AND(A=E,B=F,C=G),1,difference formula)
      here A,B,C contains date 1 and E,F,G contains second date.

  25. Hi, I need help if someone knows about excel formulas. I have an excel sheet that has an ordered date and days been ordered. But some of the cells do not have requested date, in that case, I need the cells to be empty.

  26. Count days from today. If the call is blank, shows nothing.

  27. Hi I am really stryggling with the correct formulas for these columns. I think I have them done correctly, but can't seem to figure out how to get the "Days Complete" and the "Days Remaining" in real time. So if it is completed, it should show 0, and if compared to the present I don't want it to show a negative number but just a zero for completion. HELP PLEASE!! Here are my columns and their values:

    START DATE 12/18 DAY OF MONTH* 18 END DATE 1/4 DURATION*(WORK DAYS) 14 DAYS COMPLETE* 14 DAYS REMAINING*-56 TEAM MEMBER ABC PERCENT COMPLETE ?

  28. Hi...is there a way to Calculate the # of workdays between 2 dates & then allocate them by month? I just can't seem to get any formula to work?

  29. 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.

  30. 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

  31. 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.

  32. 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

  33. 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

  34. 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

    1. 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.

      1. What if I wanted to use this formula with "B1" being "Today"? I can't seem to make that work.

  35. 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

    1. 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.

  36. I would like to subtract Sundays from the networking days calculated

  37. 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

    1. 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.

      1. Thank you Mam..

      2. Excellent, thank you; just what I needed - the date difference and a zeroed-out field while only the start date is currently known.

      3. When one column date is 30/6/2019 and once column is blank (no date) then how to check difference between two columns , i need result "0" if any formula try to check two columns difference

  38. 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

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

  40. 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?

  41. Very informative and useful for me.

    Thank you.

  42. 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.

  43. 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.

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