May
28

Excel DATEDIF - calculate difference between two dates in days, weeks, months or years

In this tutorial, you will find a simple explanation of the Excel DATEDIF function and a few formula examples that demonstrate how to compare dates and calculate the difference in days, weeks, months or years.

Over the past few weeks, we investigated nearly every aspect of working with dates and times in Excel. If you have been following our blog series, you already know how to insert and format dates in your worksheets, how to calculate days, weeks, months and years as well as add and subtract dates.

In this tutorial, we will focus on calculating date difference in Excel and you will learn different ways to count the number of days, weeks, months and years between two dates.

Excel DATEDIF function - calculating date difference

As its name suggests, the DATEDIF function is purposed for calculating the difference between two dates.

DATEDIF is one of very few undocumented functions in Excel, and because it is "hidden" you won't find it on the Formula tab, nor will you get any hint on which arguments to enter when you start typing the function's name in the formula bar. That is why it's important to know the complete syntax of Excel DATEDIF to be able to use it in your formulas.

Excel DATEDIF function - syntax

The syntax of the Excel DATEDIF function is as follows:

DATEDIF(start_date, end_date, unit)

All three arguments are required:

Start_date - the initial date of the period you want to calculate.

End_date - the ending date of the period.

To put it differently, start_date and end_date are two dates to calculate the difference between. In your DATEDIF formulas, you can input the dates is various ways, such as:

  • Cell references. For example, the following formula counts the number of days between the dates in cells A1 and B1:

    =DATEDIF(A1, B1, "d")

  • Text strings. Excel understands dates in many formats, for example "20-May-2015", "5/20/2015", "2015/5/20", etc. The following formula calculates the number of months between the specified dates:

    =DATEDIF("5/10/2015", "7/20/2015", "m")

  • Serial numbers. Since Microsoft Excel stores each date as a serial number beginning with January 1, 1900, you can put numbers corresponding to the dates directly in the formula. Although fully supported, this method is not reliable because date numbering varies on different computer systems. In the 1900 date system, the following formula is another way to calculate months between two dates from the previous example (10-May-2015 and 20-Jul-2015):

    =DATEDIF(42134, 42205, "m")

  • Results of other functions. For instance, the following formula counts how many days there are between today's date and 20 May, 2015.

    =DATEDIF(TODAY(), "5/20/2015", "d")

Note. In your formulas, the end date must always be greater than the start date, otherwise the Excel DATEDIF function returns the #NUM! error.

Unit - the time unit to use when calculating the difference between two dates. By supplying different units, you can get the DATEDIF function to return the date difference in days, months or years. Overall, 6 units are available, which are described in the following table.

Unit Meaning Explanation
Y Years Number of complete years between the start and end dates.
M Months Number of complete months between the dates.
D Days Number of days between the start date and end date.
MD Days excluding years and months The date difference in days, ignoring months and years.
YD Days excluding years The date difference in days, ignoring years.
YM Months excluding days and years The date difference in months, ignoring days and years.

Hopefully, the above information has been helpful to understand the basics. And now, let's see how you can use the Excel DATEDIF function to compare dates in your worksheets and return the difference.

Excel DATEDIF formulas to calculate date difference in days

If you observed DATEDIF's arguments carefully, you've noticed that there exist 3 different units for counting days between the dates. Which one to use depends on exactly what your needs are.

Example 1. Count the number of days between two dates

Supposing you have the start date in cell A2 and the end date in cell B2 and you want Excel to return the date difference in days. A simple DATEDIF formula works just fine:

=DATEDIF(A2, B2, "d")

Provided that a value in the start_date argument is less than in end_date. In case the start date is greater than the end date, the Excel DATEDIF function returns the #NUM error, as in row 5:

Counting the number of days between two dates

If you are looking for a formula that can return the date difference in days as either a positive or negative number, simply subtract one date directly from the other:

=B2-A2

Please see How to subtract dates in Excel for full details and more formula examples.

Example 2. Count days in Excel ignoring years

Supposing you have two lists of dates that belong to different years and you wish to calculate the number of days between the dates as if they were of the same year. To do this, use a DATEDIF formula with "YD" unit:

=DATEDIF(A2, B2, "yd")
Excel DATEDIF formula to count days between two dates ignoring years

If you want the Excel DATEDIF function to ignore not only years but also moths, then use the "md" unit. In this case, your formula will calculate days between two dates as if they were of the same month and the same year:

=DATEDIF(A2, B2, "md")

The screenshot below demonstrates the results, and comparing it with the screenshot above can help understand the difference better.
DATEDIF formula to calculate days between two dates ignoring years and months

Tip. To get the number of working days between two dates, use the NETWORKDAYS or NETWORKDAYS.INTL function.

Calculating date difference in weeks

As you probably noticed, the Excel DATEDIF function does not have a special unit to calculate date difference in weeks. However, there is an easy workaround.

To find out how many weeks there are between two dates, you can use the DATEDIF function with "D" unit to return the difference in days, and then divide the result by 7.

Calculating date difference in weeks in Excel

To get the number of full weeks between the dates, wrap your DATEDIF formula in the ROUNDDOWN function, which always rounds the number towards zero:

=ROUNDDOWN((DATEDIF(A2, B2, "d") / 7), 0)

Where A2 is the start date and B2 is the end date of the period you are calculating.
Calculating how many full weeks there are between two dates

How to calculate months between two dates in Excel

Similarly to counting days, the Excel DATEDIF function can compute the number of months between two dates that you specify. Depending on the unit you supply, the formula will produce different results.

Example 1. Calculate complete months between two dates (DATEDIF)

To count the number of whole months between the dates, you use the DATEDIF function with "M" unit. For example, the following formula compares the dates in A2 (start date) and B2 (end date) and returns the difference in months:

=DATEDIF(A2, B2, "m")

Note. For the DATEDIF formula to calculate months correctly, the end date should always be greater than the start date; otherwise the formula returns the #NUM error.

DATEDIF formula to calculate months between two dates

To avoid such errors, you may force Excel to always perceive an older date as the start date, and a more recent date as the end date. To do this, add a simple logical test:

=IF(B2>A2, DATEDIF(A2,B2,"m"), DATEDIF(B2,A2,"m"))

Example 2. Calculate months between two dates ignoring years (DATEDIF)

To count the number of months between the dates as if they were of the same year, type "YM" in the unit argument:

=DATEDIF(A2, B2, "ym")
Calculating months between two dates ignoring years

As you see, this formula also returns an error in row 6 where end date is less than the start date. If your data set may contain such dates, you will find the solution in the next examples.

Example 3. Calculating months between two dates (MONTH function)

An alternative way to calculate the number of months between two dates in Excel is using the MONTH function, or more precisely a combination of MONTH and YEAR functions:

=(YEAR(B2) - YEAR(A2))*12 + MONTH(B2) - MONTH(A2)

Of course, this formula is not so transparent as DATEDIF and it does take time to wrap your head around the logic. But unlike the DATEDIF function, it can compare any two dates and return the difference in months as either a positive or negative value:
Calculating months between two dates using the MONTH and YEAR functions

Notice that the YEAR/MONTH formula has no problem with calculating months in row 6 where the start date is more recent than the end date, the scenario in which an analogues DATEDIF formula fails.

Note. The results returned by DATEDIF and YEAR/MONTH formulas are not always identical because they operate based on different principles. The Excel DATEDIF function returns the number of complete calendar months between the dates, while the YEAR/MONTH formula operates on months' numbers.

For example, in row 7 in the screenshot above, the DATEDIF formula returns 0 because a complete calendar month between the dates has not elapsed yet, while YEAR/MONTH returns 1 because the dates belong to different months.

Example 4. Counting months between 2 dates ignoring years (MONTH function)

In case all of your dates are of the same year, or you want to calculate months between the dates ignoring years, you can the MONTH function to retrieve the month from each date, and then subtract one month from the other:

=MONTH(B2) - MONTH(A2)

This formula works similarly to Excel DATEDIF with "YM" unit as demonstrated in the following screenshot:
Counting months between 2 dates ignoring years with MONTH function

However, the results returned by two formulas differ is a couple of rows:

  • Row 4: the end date is less than the start date and therefore DATEDIF returns an error while MONTH-MONTH yields a negative value.
  • Row 6: the dates are of different months, but the actual date difference is just one day. DATEDIF returns 0 because it calculates whole months between 2 dates. MONTH-MONTH returns 1 because it subtracts the months' numbers from each other ignoring days and years.

How to calculate years between two dates in Excel

If you followed the previous examples where we calculated months and days between two dates, then you can easily derive a formula to calculate years in Excel. The following examples can help you check if you got the formula right :)

Example 1. Calculating complete years between two dates (DATEDIF function)

To find out the number of complete calendar years between two dates, use the old good DATEDIF with the "Y" unit:

=DATEDIF(A2,B2,"y")

DATEDIF function to calculate complete years between two dates

Notice that the DATEDIF formula returns 0 in row 6, although the dates are of different years. This is because the number of full calendar years between the start and end dates equals to zero. And I believe you are not surprised to see the #NUM! error in row 7 where the start date is more recent than the end date.

Example 2. Calculating years between two dates (YEAR function)

An alternative way to calculate years in Excel is using the YEAR function. Similarly to the MONTH formula, you extract the year from each date, and then subtract the years from each other:

=YEAR(B2) - YEAR(A2)

In the following screenshot, you can compare the results returned by the DATEDIF and YEAR functions:
Calculating years between two dates using the YEAR function

In most cases the results are identical, except that:

  • The DATEDIF function calculates complete calendar years, while the YEAR formula simply subtracts one year from the other. Row 6 illustrates the difference.
  • The DATEDIF formula returns an error if the start date is greater than the end date, while the YEAR function returns a negative value, as in row 7.

How to calculate date difference is days, months and years

To count the number of complete years, months and days between two dates in a single formula, you simply concatenate three DATEDIF functions:

=DATEDIF(A2, B2, "y") &" years, "&DATEDIF(A2, B2, "ym") &" months, " &DATEDIF(A2, B2, "md") &" days"
Calculating date difference is days, months and years

If you'd rather not display zero values, you can wrap each DATEDIF in the IF function as follows:

=IF(DATEDIF(A2,B2,"y")=0, "", DATEDIF(A2,B2,"y") & " years ") & IF(DATEDIF(A2,B2,"ym")=0,"", DATEDIF(A2,B2,"ym") & " months ") & IF(DATEDIF(A2, B2, "md")=0, "", DATEDIF(A2, B2, "md") & " days"

The formula displays only non-zero elements as demonstrated in the following screenshot:
Calculate date difference is days, months and years ignoring zero values

DATEDIF formulas to calculate age in Excel

In fact, calculating someone's age based on the date of birth is a special case of calculating date difference in Excel, where the end date is today's date. So, you use a usual DATEDIF formula with "Y" unit that returns the number of years between the dates, and enter the TODAY() function in the end_date argument:

=DATEDIF(A2, TODAY(), "y")

Where A2 is the birth date.

The above formula calculates the number of complete years. If you'd rather get the exact age, including years, months and days, then concatenate three DATEDIF functions like we did in the previous example:

=DATEDIF(B2,TODAY(),"y") & " Years, " & DATEDIF(B2,TODAY(),"ym") & " Months, " & DATEDIF(B2,TODAY(),"md") & " Days"

And you will get the following result:
DATEDIF formula to calculate age in Excel

To learn other methods of converting a birthdate to age, check out How to calculate age from date of birth.

This is how you compute the difference between two dates in a variety of time intervals. Hopefully, the DATEDIF function and other formulas you've learned today will prove useful in your worksheets. Anyway, I thank you for reading and invite to check out other parts of our tutorial to working with days and time in Excel.

You may also be interested in:

150 Responses to "Excel DATEDIF - calculate difference between two dates in days, weeks, months or years"

  1. Louie says:

    Hi. i just wondering if I cannot send an attachment with regards to problem in calculating the days( dates). So that I can show you the spreadsheet.
    They asking me to calculates the number days since the student say the exam. If the student has not yet sat the exam, instruct the formula to display a hyphen.
    The formula that used is DATEDIF but my instructor said its wrong. I hope you can help me. Thank you. > Louie

    • Hi Louie,

      We are by all means avoiding posting our email address anywhere because of spammers. So, can you please post the same question on our forum and attach your workbook. Our support team will do their best to help.

  2. Louie says:

    If I can send an attachment I mean. Thank you

    • Louie,

      You can attach a file when posting a question on our forum. You just have to register (create a user account), then click the blue "Start New Topic" button in the upper right corner, and then click the Attach Files button in the lower left corner.

  3. Louie says:

    Hi Svetlana, I already posted my topic : Essential Function.
    Hope you can help me. Thank you again > Louie

  4. Louie says:

    Let me know if you receive it love. Thank you

  5. Sazedul says:

    Hi

    Please give the formula in excel how do I convert a number in different row year month and day, tried my best but failed to get the solution. Suppose give me the solution below -

    Day Year Month Day
    400 ? ? ?

    Regards
    Sazedul

  6. Sam says:

    Hi, I'm trying to set up a table where the difference between dates is needed, however I need to be able to break this down into months (the table to heave headers for the months). For example, if we have a start date of 01/04/14 and an end date of 07/05/14, is there a formula I can use which will automatically give me 30 days in one cell for April, and 7 Days in another cell for May?

  7. Lindsay says:

    Hi Svetlana,

    I was looking for a similar Excel formula as Sam and found that this one will only work if the date range are in the same year.

    How would the formula change if the dates were in different years? For example if I need the days totaled for April 2015, May 2015, and June 2015 from the date range of 23-Sep-2014 to 4-Dec-2015?

    Would this be possible to do?

    Thanks for your help!

  8. Gregg says:

    Hi Svetlana,

    Your improved formula for Linday is great comment-162067.xlsx. However, is it possible to exclude the holiday/s in a particular month/year?

    Thanks in advance.

  9. Dante says:

    I need my code to hide a row once the date entered is 30 days past current date. Also all the cells are not filled, the date will be enter later I don't want too see value errors in the blanks.

    How can this be done?

  10. Maninder says:

    I have a particular date for a particular task and if the date has past 2 days from the bench mark date then how can I put a formula to highlight that this particular task has been already passed 2 days so that I can chase for the same.

  11. Abby says:

    Hi Svetlana,

    I used the formula "Datedif" both for Month and year, it's missed one Month or one Year. Eg. 01/Jan/2015 and 31/Dec/2015 the logical is 12 Months but the answer of the formula is 11 Months (Wrong).

    How can this be done?

    Thanks.

    Regards
    Abby

    • ali says:

      did you find the an answer , I am also facing the same issue. thanks

      • Jey says:

        Hi abby & Svetlana,

        did you get the answer, i also face the same issue.
        start date is 1/7/15 and end date is 30/6/15.
        the formula answer only 11 month. It suppose to be count as 12 month.

        could you please share idea to counter the problem.

        thank you.

        • Kelvin says:

          I was facing the same problem of getting the correct month difference between 2 dates. For example:

          30/09/2016 - 31/10/2016 - Datedif given me 1 month difference, but then
          31/10/2016 - 30/11/2016 - Datedif given me 0 month differece.

          This showed inconsistency of computation.

  12. ashish says:

    sir,any formula u have which calculate days among four dates.

  13. Chadd says:

    How would you take the todays date in A1 subtract that from all dates posted in Column D and post the days till in Column G?

  14. Adam says:

    Hi there,

    I've been trying to find a formula which calculates the number of weeks and days from 2 dates.

    Can you help?

  15. Brenda says:

    I am trying to find a formula that will turn a certificate name cell green if a date is less than 9 months old, yellow if the date is between 9 and 10 months old, orange if it is between 10 and 11 months old and red if it is over 11 months old. this is for one year expiration but I want to apply it to 2, 3 and 4 year expiration dates as well.

  16. Anita says:

    Good afternoon....I'm trying to "correct" a formula that we need to calculate how many months in a year an employee has worked...so there are multiple start and end dates. Currently the formula is =(DAYS360(D2,E2)+DAYS360(F2,G2)+DAYS360(H2,I2)+ DAYS360(J2,K2)+DAYS360(L2,M2)+DAYS360(N2,O2))/30 which is pretty good except when the employee has a start date and "no" end date....I get a negative number. For instance: Start 1/5/2015 End 3/23/2015 Start 3/23/2015 End 8/28/2015 Start 10/5/2015...I get a -1381.4. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any assistance would be greatly appreciated.

  17. SUMESH says:

    =DATEDIF(K4,J4,"D") IS NOT WORKING IN MY EXEL CAN U HELP ME
    DATE FORMAT IS SHOWN UNDER
    14/05/2015 30/05/2015

  18. Anita says:

    Hello Svetlana....Good afternoon....I'm trying to "correct" a formula that we need to calculate how many months in a year an employee has worked...so there are multiple start and end dates. Currently the formula is =(DAYS360(D2,E2)+DAYS360(F2,G2)+DAYS360(H2,I2)+ DAYS360(J2,K2)+DAYS360(L2,M2)+DAYS360(N2,O2))/30 which is pretty good except when the employee has a start date and "no" end date....I get a negative number. For instance: Start 1/5/2015 End 3/23/2015 Start 3/23/2015 End 8/28/2015 Start 10/5/2015...I get a -1381.4. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any assistance would be greatly appreciated.

  19. Fjan says:

    how to change date format 26/10/2015 in to 26-Oct-2015 using the excel formula.. kindly send me ans

  20. Muhammad Qasim says:

    i am looking to find the formula of leave management system in excel. i have pick Date calender from developer option and drop in to 2 cells like C20 start date of leave and G20 End date of Leave. In other cell i have 30 casual leaves in 1 year now i am coming to the point when i will select start date of leave and end day of Leave the Automatically Cut leaves from the other 30 casual leave is it possible./.....

  21. Carmeister says:

    Or if like me you would like to see a dynamic formula that takes into account the differnce in multiple months for example then the formula could be adapted as follows:

    =IF(DATEDIF(A2,B2,"y")=0,"",DATEDIF(A2,B2,"y")&" years ")&IF(DATEDIF(A2,B2,"ym")=0,"",DATEDIF(A2,B2,"ym")&IF(OR(DATEDIF(A2,B2,"ym")>1)," months "," month ")&IF(DATEDIF(A2,B2,"md")=0,"",DATEDIF(A2,B2,"md")&" days"))

  22. Rajendra says:

    This guidance was superb. But if I consider all months of 30 days (360 days for year) how can I calculate difference between two dates in days / months/ years etc?

  23. jagadeesh.rt says:

    DATE DATE DAYS
    1/8/2016 4/7/2016 90.00 (=DATEDIF(C5,D5,"D")

    In the above case 2016 Leap year where DATEDIF missing 29th February, 2016 i.e. output result should be 91 days and not 90 days

    In case of manual dates calculation = 91 days is exact result.

    Can any one help DATEDIF (days/month/year) in caese leap year.

  24. vinn says:

    hi may i know how to count a set of datas in weeks

  25. Abdur Rahim says:

    Hi
    May i know how to calculate in date two between date,month and year ?Please give me example and formula.I wish u all the best.

  26. KM says:

    Hi, I would like to have my result be in decimal form when calculating time between two dates. Example 3.7 years

  27. Chirag P says:

    Hi,

    same question as KM, but on days, how can I get days in decimals, i.e., 1.47 days

    Thanks

  28. Deepak Verma says:

    very useful....and very helpful for all champs...

  29. Nabin Sarkar says:

    Date of join Present Date Year Month Day
    22/01/2012 13/01/2016 3 11 135
    I am facing the problem in Day cell. Why it shows 135 days in day cell ? Please solve this problem. I am using Excel 2007.

  30. Robert says:

    Using that formula from above : =DATEDIF(A1,A2,"y") & " years, " & DATEDIF(A1,A2,"ym") & " months, " & DATEDIF(A1,A2,"md") & " days"
    Put 3/12/2015 in A1, and 1/11/2016 in A2. You'll get a result of 0 years, 9 months, 143 days. Why?
    Make A2 1/17/2016 and you get 0 years, 10 months, 5 days.

    Column A is formatted as Date, while the results column is General.
    Excel 2007 SP3. Win7 Pro (same result on Server 2008 R2)

  31. Agim says:

    Hi Maria,
    I try to calculate for 12 month the annual leave for worker who work in my company example:
    Januar, februar, March,April and worker used in januar with date 4 day in march used 6 days i lile to know total days with date start and end date diference can you help me please.

    Agim

  32. Denisa says:

    Hello,

    I have the following situation: Sheet 1 contains a list of articles (code and name), with Start delivery date, End delivery date, and a promotional price which applies only between those dates.
    Sheet 2: a database with all the articles delivered, date of delivery and delivery price.

    I need the following: in sheet 2, I need to bring the promo price for a certain article from sheet 1, if the article was delivered between the dates (start and end delivery date).

    Please help!

  33. Kong says:

    I need to calculate time difference for 500 different dates in column B and column A in year decimal point. Is there a formula that will calculate all 500 time difference and present it in column C?

  34. Natalie says:

    Hi, I am trying to find out the difference between two dates. The dates refer to the expected end date and the actual end date, and I want to know how many have gone over time and how many days it has gone over. When I use DATEDIF above the dates where there would be a 'negative' result run as an error.

    • Hi Natalie,

      If you are calculating the difference in days, then you can simply subtract one date from the other and get the result either as a positive or negative number.

      If you don't want negative numbers, then you can use a nested If formula similar to this:
      =IF(A2>B2, DATEDIF(B2,A2,"d"), DATEDIF(A2,B2,"d"))

  35. tawaphol says:

    Hi Cheysheva

    wonder if you can guide me how to count date and time different for working hours only? (0900-1700)

    your help would be greatly appreciated...

  36. Chantal says:

    Hi there
    I need assistance with a formula. Setting up the excel file to calculate the lead-time from when the product was ordered to when it was received, however the cell must over-ride so that the calculated figure is always divisible by 7 eg. actual lead-time calculated is 23 days, however new rule is to formulate it to the 7 day = 21 days

  37. Kelli says:

    Need to count how many days in a contract will occur during a specific period of time. Example: contract runs 9/2/2014 - 8/31/2015 during the period of 9/1/2014 - 9/28/2014.

  38. Rama says:

    i want a formula to calculate interset on quarterly basis from the date of investment

  39. Dilli Ganesh says:

    Its Good
    Thank you !

  40. Sinéad says:

    Hi I am using an incident/accident database.
    I have the date of the accident in C1
    The date returned to work L1
    I want to count the number of weekdays missing however I want to count the days missing in the month that the accident happened and days carried over to the next month.

    Eg if accident happened 29/01/2016 returned to work 05/02/2016
    how may weekdays were missing in January?
    how many weekdays were missing in February?

    Thanks and hope this makes sense

  41. Ben says:

    Hi there,

    I was wondering if there is a way to sort out financial years between 2 dates. EG 01/01/2013 to 19/10/2015. We know these dates span 4 financial years. Is there a way to allow excel to sort these dates so they appears like the below:

    01/01/2013 to 30/06/2013
    01/07/2013 to 30/06/2014
    01/07/2014 to 30/06/2015
    01/07/2015 to 19/10/2015

    Thanks!

  42. Swapnil says:

    Hi friends,

    I want to find age from TODAY() but date format is below in a cell
    ----------A--------------------!
    Serial no. Date Time Age
    8505642244 29-02-2016 11:30AM! ?
    8505642249 26-02-2016 11:30AM ?
    8505642444 20-02-2016 11:30AM ?

  43. Swapnil says:

    Hi friends,

    I need to find age from TODAY() but date format is below in a cell(Serial no. Date Time) & (Age) and there needs to compare with current date and cell date into age.
    =============================================
    ----------A-COLUMN-------------!--B-COLUMN--!
    =============================================
    Serial no. Date Time ! Age !
    8505642244 29-02-2016 11:30AM ! ? !
    8505642249 26-02-2016 11:30AM ! ? !
    8505642444 20-02-2016 11:30AM ! ? !
    =============================================

    Can it possible? if yes, please share.

    Thanks!

  44. Aruna says:

    Guys, can anyone help me with this.

    i have 2 dates Call confirmed - 01/02/2016 11:47 & Dispatched - 05/02/2016 13:50. i want to calculate how many days hours and minutes. the result should be in this format 0 days 0 hours 0 minutes. please help with this.

  45. Maki says:

    Hi guys,

    I am working in big excel, it has more than 2M records, I am using PowerPivot. I am trying to aggregate the data by day. I have a lot of transaction in a day, I want to group that. I tried to use the following DAX but it doesn't seem to work...I am new for this...any tips?

    =COUNT([column name],CALENDAR(2016,1,31.2016,1,31))

  46. Maruti Thakur says:

    Parameters:
    Item Start Date End Date Value
    A 01-Apr-11 02-Feb-12 2
    A 03-Feb-12 01-Mar-12 3
    A 02-Mar-12 31-Dec-13 4
    A 01-Jan-14 31-Jan-14 5
    B 09-Jan-13 04-Apr-14 6
    B 05-Apr-14 07-Feb-15 7
    B 08-Feb-15 01-May-16 8
    B 02-May-16 01-Jun-16 9

    Item Date Value
    A 30-Apr-12
    A 05-Feb-12
    A 30-Jan-14
    B 07-Apr-14
    B 20-Feb-15

    Please help for for the above value column based on provided parameters.

  47. Nani says:

    Hello Svetlana...Good Morning....I'm trying to "correct" a formula that we need to calculate how to calculate date difference is days, months and years...so there are 2 dates start and end date. Currently the formula is =IF(A1="","", DATEDIF($A1,$B1,"md")&" days. "&DATEDIF($A1,$B1,"ym")&" m."&DATEDIF($A1,$B1,"y")&" y.")) Which is pretty good but when the data has a start date and "no" end date....I get a #NUM! error.
    For example: 1) Start 1/1/2016 End 2/15/2016 ... I get a 14 days. 1 m.0 y. it is good.
    2) Start 1/1/2016 End Blank .... I get a #NUM! error. I was thinking there would need to be a Today() somewhere in the formula but cannot get anything to work. Any support would be greatly appreciated.

    • Hello Nani,

      You were looking at the right direction. You can add one more IF statement to your formula, which will calculate the difference between the start date (A1) and Today() if there's a date in A1 but B1 is empty:

      =IF(AND(A1<>"", B1=""), DATEDIF($A1,TODAY(),"md")&" days. "&DATEDIF($A1,TODAY(),"ym")&" m."&DATEDIF($A1,TODAY(),"y")&" y.", IF(A1="", "", DATEDIF($A1,$B1,"md")&" days. "&DATEDIF($A1,$B1,"ym")&" m."&DATEDIF($A1,$B1,"y")&" y."))

  48. Ramu says:

    HI...can anyone help me with a formula for aging data in days (to be aged based on current date vs. for eg., Invoice date)

  49. Rachel says:

    Hi - can anyone help me with a formula,
    I want to calculate the number of days between Today and Column M. I want to display positive and negative numbers but sometimes there is no date in column M.
    If I use =TODAY()-M1, I get a #VALUE error if no date
    If I use ==DATEDIF(Today(), B2, "d"), I get a #NUM error for negative output.

    Please help!

  50. Jay says:

    I'm using the datedif formula to initially calculate days and then weeks (DATEDIF(X11,Y11,"d")/7. I calculated weeks for the entire year, 1/1/16-12/31/16, and came up with 52.14. However, I need to do it by month - and when I come up with a total number of weeks, using the same formula, the total for the year is 50.57 weeks. I'm off by 1.57 weeks. What am I doing wrong? This exercise is being used to determine temp costs.

    Many thanks

  51. Serene says:

    I'm trying to calculate the amount of time in years someone has worked here but that number needs to change based on the date the excel sheet is opened. So if I open this sheet next month, the numbers will change to show the time worked calculated from their start date to whatever date I open the file next month. Likewise, if I open the file 6 months from now, the number of years worked will change accordingly to include the past 6 months. Not sure I'm explaining this well but I hope you understand.
    Thanks in advance for your help.

  52. Tujju says:

    I can't find DATEDIF function. It is only showing me DATE and DATEVALUE.

    • Hi Tujju,

      DATEDIF is one of few undocumented functions in Excel, which don't appear either in the formula list, or when you start typing in the formula bar. To make a formula, you need to type all the arguments manually.

  53. Colin Jarrett says:

    DATEIF calculates number of months between 1st Jan 2015 and 31 Dec 2015 as 11 months. How can I adjust the formulate to calculate the answer as 12 months. Thanking you very much for your assistance.

    • Dan says:

      Hi Colin,

      I had this issue also. Turns out the fix is quite simple, just add "+1" to the end of the formula. =DATEDIF(A1,B1,"m")+1

      For some reason the format of the cell seems to randomly amend to date, but you can simply amend back to number if this happens.

      Hope this helped!

      Dan

  54. Alfred says:

    Hi,

    I get an error message each time I try to use datedif function. The error message says: "The formula you typed contains an error"

    Can anyone help me with this?

    Alfred

  55. Sebastian says:

    I have hit a brick wall I need to calculate the difference between the current date(day) and a past fixed date or a future fixed date so as to calculate moths post contract expiry date (negative) or how many moths to contract expiration date(positive)

  56. Sebastian says:

    I have hit a brick wall I need to calculate the difference between the current date(day) and a past fixed date or a future fixed date so as to calculate months post contract expiry date (negative) or how many months to contract expiration date(positive)

    Reply

  57. Nathan says:

    This is epic!
    Working with this one:
    How to calculate date difference is days, months and years

    Is there a way to calculate the max, min, and average time?

    Thank you so much!

  58. nataly says:

    thank you. very helpful.

  59. R.K.PRIYA (DEO, IDSP) SUPAUL, BIHAR says:

    THANK YOU !

  60. brscpa.com says:

    The DATEDIF function does not appear in the default installation of MS Office 2010. Is there a way to import this function, or update Excel 2010 to add this function?

  61. shraddha says:

    5/25/2016 -8/31/2017 if between these two dates i want to find no of days in may then june and so respectively how do i do that. for example in may the number of days are 5 . i want the formula for this.

  62. Stacey says:

    I am using this formula and the result is not correct. For example, I'm calculating the number of months between 3/26/2013 - 1/30-2016, which is logically 2.10. I use the formula =ROUND((DATEDIF(G2,J2,"M")/12), 1)and the result is 2.8. The cell G2 contains the date 3/26/2013 and J2 contains the date 1/30/2016. Where did I go wrong?

  63. Kapildev says:

    Hi Svetlana,

    kindly help, i put different date in one column i need the date to date between days
    For example Column A i give different date 1 to 10 cell now i need column B the different date to date days
    Column A Column B
    11-May-16
    13-May-16 ?days
    17-May-16 ?days
    1-Jun-16 ?days

  64. Shay says:

    I have a question... that kind of go along these lines...

    I have my formula: =DATEDIF(D3,TODAY(),"D")

    This is giving me the aging that I desire...

    The problem is, I want to be able to keep this aging... but I also want to stop the counter, when I plug in a completion date... of a given task...

    Any thoughts~

    • Hi Shay,

      I think you can use a formula similar to this:
      =IF(E3<>"", DATEDIF(D3,E3,"d"), DATEDIF(D3,TODAY(),"d"))

      Where E3 is the completion date. If there is a date in E3, the formula calculates the difference between D3 and E3, otherwise between D3 and today's date.

  65. Brian says:

    how are you,may you pliz send me a worked downloadable example on hw to calculate days between two dates.

  66. Ritesh Rai says:

    Hi Svetlana

    Could you please help me with the formula.
    I have used data validation. All I want is if it says says. I want a date to be populated after 7 business days. If no I want the date after 5 business days to be populated in a cell.

  67. ravinder says:

    HI
    IN CONDITIONAL PARAMETER

    01 APRIL 2015 TO 30 JUNE 2015 = IS 1ST QTR
    01 JULY 2015 TO 30 SEPTEMBER 2015 = IS 2ND QTR

    01 OCTOBER 2015 TO 30 DECEMBER 2015 = IS 3RD QTR

    01 JANUARY 2015 TO 30 MARCH 2015 = IS 4TH QTR

    IF WE PUT THE DATE AS WE DESIRE IT WILL AUTOMETIC PRINT/DISPLAY IT QTR

  68. Loretta says:

    I have simple formula to calculate number of days between dates =DAYS(G266,C266). If there is no date yet in G column the result in the H column with the formula is -42412 or #VALUE!. What formula in the H column cell blank when there is no date yet entered?
    Thanks!

  69. Kat says:

    I'm trying to calculate the number of days a task is overdue. C2 contains the date the assignment is due and d2 is the date the assignment was completed. Example:

    Assignment Due Date Assignment Completion Date Days Overdue
    5/9/2016 5/2/2016 0
    5/10/2016 5/13/2016 3

    I need to be able to calculate the number of days overdue. What formula do I use? I know I can't use the DATEIF formula. Thank you for your help.

  70. Aakash D V says:

    Can you Please help me out with the following problem.I need to get two values from two different cells and combine it in one cell with certain preexisting characters.
    Ex: Cell A1 contains "Aakash" Cell A2 contains "D V". I need final expression in cell A3 as "UI-Aakash D V". Please let me know if it is possible and if it is kindly let me know how.

  71. Alex says:

    If the second date is empty and I want the value to remain blank or say not complete how do I set that up. I am getting #value

  72. edgar says:

    I have a spreadsheet that provides the date of when an business item is received for processing. And a second column for a date when the item is submitted for processing. I would like a third column that highlights each item where the difference between receipt and submission is greater than 3 working days. Thank you for your assistance.

  73. Drasko says:

    Hello Svetlana,

    Can you Please help me out with this: I need to calculate number of working years, months and days between two dates with break.
    EX: If few employee start working at one date and working certain period then left company and after few years comeback. With DATEDIF function using SUM function getting wrong result.
    Please let me know if it is possible and if it is kindly let me know how.

  74. Dimitris says:

    Hello Svetlana,
    Please could you assist me in my below calculation because I am struggling 20 hours and cannot get it using many formulas.
    I have this formula : Cell D44 03:15:30 (where dd:hh:mm) - Cell D45 00:10:52 (where dd:hh:mm). I want to get the result in the cell D46, in case that D45 is greater then appears ####### I tried many solution but they didn't work.
    Additional, when I form the cell D46 with different colour based on the result then when I close the Worksheet and re-open it again the colour hasn't remained as I formed. I don't want to use the form 1904, I used it but the dates were not those I wanted to get. I used Excel 2011 in Mac computer.
    Thanks a lot in advance.

  75. Erica says:

    Hi,

    I am trying to find the chronological age in years and months between a birthday and a testing date.

    For example:
    Birth Date: 3-3-2001
    Test Date: 6-29-2007
    Then the age would be: 6 years 3 months

    Birth Date: 8-2-1999
    Test Date: 5-8-2006
    Then the age would be: 7 years 9 months

    Birth Date: 11-24-1985
    Test Date: 8-13-2006
    Then the age would be: 21 years 8 months

    Is this possible? Thanks so much!

  76. Torey Wihlidal says:

    I am not great at using Excel, so I apologize if this question seems a little basic, but I need help. I have a spreadsheet that has two columns of dates. I would like to use datedif to automatically calculate the difference between these sets of dates into a third column without having to type the formula into each cell. Is there a way to do this?

    • Hello Torey,

      Of course, you don't have to type the formula in each cell manually. Just write the DATEDIF formula for the top cell using relative cell references (without the $ sign, like A1). Then double click a small square at the lower right-hand corner of the formula cell (it will change to the plus sign as soon as you hover over it), and the formula will copy across the entire column.

      You can find a handful of other ways to copy formulas in Excel here: How to copy a formula in Excel.

  77. sikandar says:

    Hi
    Svetlana

    Thank you, but I do not have good English helped me to formula

    =IF(DATEDIF(A2,B2,"y")=0, "", DATEDIF(A2,B2,"y") & " years ") & IF(DATEDIF(A2,B2,"ym")=0,"", DATEDIF(A2,B2,"ym") & " months ") & IF(DATEDIF(A2, B2, "md")=0, "", DATEDIF(A2, B2, "md") & " days"

    Thank you,Thank you

  78. kasi says:

    How to convert "2 years 10 months" into number of days

  79. FATMA LB says:

    Hi,

    I want to know which formula I have to use if I want to calculate no. of days before a specific date, and total no. of days after or from specific date

    for ex: if am calculating number of sick leave days for employee per year and different period of sick leave given.

    Regards

  80. Tina says:

    Hi

    I want to know the formula which will just give year and month where month is not in 2 decimal. Here is the formula which I have used =DATEDIF(E7,TODAY(),"Y")& "."&" "&DATEDIF(E7,TODAY(),"YM")

    and result is 0.10 which is 10 months, with this result I am not able to filter the file in chronological order.

    Is there a way to resolve this problem.

  81. Cheryl says:

    Thanks, it was really helpful!!!

  82. Jadran says:

    Hi!
    My question: first date = 12.02.2000, second date = 1.5.2012. How to get in excel 2007 or 2013 the number of days of leap years ?
    (The number of these days should be 324+366+366+121 = 1177 days).
    Thanks to the possible response.

  83. wahidullah Rahmani says:

    Very good formula, thanks for this

  84. Md. Shahin Shahriar says:

    If i give those Date and time, Then what difference between those day's and time it is ?

    Give an Example:

    Two date with time....
    Start date & time: 11:39 PM 12/2/2016
    end date & time: 01:39 AM 12/3/2016

    Then, How is differentiating two identities in excel.

    I want just actual time..........

    Please help me........

  85. Niall Thomas says:

    Hi,

    I need this code to exclude duplicates from the previous 10 days inclusive.

    =IF(COUNTIFS(A$2:A$20002,A2,B$2:B$20002,B2)>1,"1","")

    I'm counting duplicate errors but I takes circa 10 days to close an error so I need to exclude those.

    Thanks,

    Niall

  86. sam says:

    ABA CHAVHAN Nov-14
    ABA CHAVHAN Dec-14
    ABA CHAVHAN Jan-15
    ABA CHAVHAN Feb-15
    ABA CHAVHAN Mar-15
    ABA CHAVHAN Apr-15
    ABDULRAHIM S KHAN Dec-14
    ABDULRAHIM S KHAN Jan-15
    ABDULRAHIM S KHAN Feb-15
    ABDULRAHIM S KHAN Mar-15
    ABDULRAHIM S KHAN Apr-15
    ABDULRAHIM S KHAN May-15
    AJAY APPA GAIKWAD Nov-14
    AJAY APPA GAIKWAD Dec-14
    AJAY GAGAT Nov-14
    AJAY GAGAT Dec-14
    AJAY GAGAT Jan-15
    AJAY GAGAT Feb-15
    in this case which formula calculating month

  87. Taj Shaik says:

    How to get the difference between Date which contains time as well. I am looking into find the "Days difference by comparing two cells"

    Cell A: 12-13-2016 19:13
    Cell B: Current Date

    In Column C: I want to know how old is the ticket pending.

  88. Joann says:

    Hi,

    I'm currently making a company ID with an expiration date. In this case there are 2 basis for expiration date. 1 is passport expiration and the other one is visa expiration. I need to see which of the 2 expires first so that the ID validity will be based on whatever dates comes first. hoping for you response. Thanks!

  89. Donna says:

    Hi, I am trying to figure the number of years worked between hire date and the end of our next fiscal year. When I enter the formula it gives me an answer of 1/19/1900. This is the formula I am using =DATEDIF(C2, D2, "Y") C2 is the date of hire and D2 is 06/30/2018. Can someone please help me fix this?

  90. Matt B says:

    Hi there, really struggling with this...
    I am trying to determine the number of nights per week that someone has stayed in accommodation based on their check in (Ci) and check out (Co) dates compared to the week start (Ws) date and week end (We) date.
    Brackets are the named columns / rows.
    Would really appreciate your help on this as would save a lot of manual entry time!
    Many thanks
    Matt

  91. Rach says:

    D1 to D100 is the due date and I need column E to give the number of says past due date - PLEASE HELP!!

  92. Rach says:

    D1 to D100 is the due date and I need column E to give the number of days past due date - PLEASE HELP!!

  93. Kevin says:

    When I try and perform a sort based on results from this formula, it does not sort correctly. Same thing happens if I use a simple =B2-A2 equation. It looks like Excel is returning a number (as opposed to a date). Any clue why it won't sort? Any ideas for a solution?? :-)

    Thanks in advance!!!!

  94. gaurav says:

    Hi requested support. i want to calculate the number of days after close of month. if their is any support methodology for this query. Please support.

  95. Deni says:

    Hello there, I have a question. I have tried to read all posts but it's hard to know which formula I need. I have a birthdate and as of today I can figure out the exact age by year, month and day. Now, I need to know in 3 weeks from now by a specific date how old the pet will be. For instance, the birthday is 11/24/06 so I know from today how old the pet is. But now I need to know for future dates of 02/16/17, 03/15/17 and 04/20/17 exactly how old on those dates the pet will be. I have lined up the dates in the row but I keep getting errors when I try and create a formula from the birthdate...help?

  96. Alan says:

    I have just used the DATEDIF formula for Years, Months and Days. I would now like to round these figures up i.e. 14 years 30 Days = 14 years 1 Month/8 years 4 months and 30 days = 8 years and 5 months. Is there an easy way to do that or should I just revert to the DATEDIF formula for Years and Months? Many thanks.

  97. Gib says:

    Hi, I would like to know the Beginning Week Number and Ending Week Number of the month. Thank you.

    • Alan says:

      Unsure if your comment is aimed at me however I am using a start date in one column and an end date in another column. Dates starting are usually the 1st of the month and end dates are normally the last day of the month....

  98. Gib says:

    Hi Rach,

    Try this:

    =IF(A1"",DATEDIF(A1,TODAY(),"d"),"")

  99. Gib says:

    Hi Deni,

    Try this:

    =DATEDIF(A1,TODAY(),"y") & " Years, " & DATEDIF(A1,TODAY(),"ym") & " Months, " & DATEDIF(A1,TODAY(),"md") & " Days"

  100. Gib says:

    Hi, good day.

    I need help in converting the following if-statements in Excel formula:

    1. If A1 and B1(there are formulas in the column that make of them look blank, although the actual cell is not blank) are blank, then C1 is "";
    2. If A1 is not blank and B1(there are formulas in the column that make of them look blank, although the actual cell is not blank) is blank, then C1 is "Pending";
    3. If A1 and B1 are not blank, then C1 is "Completed".

    What formula can I use?

    Thank you in advance.

  101. Grace says:

    Hi, good day.

    I have a problem to calculate my rental in different period and rate.
    Eg.
    1st - 7th year : 14.09.09 - 13.09.16
    8th - 10th year: 14.09.16 - 13.09.19

    1st - 7th year : $1,000
    8th - 10th year: $750

    1st - 7th year : Splitting percentage 20%
    8th - 10th year: Splitting percentage 50%

    How to converting the above in If formula in excel?

  102. Aries says:

    Hi,

    How can I calculate together month and year in excel macro?.

    e.g.
    01-Jan-2017 (Textbox 1)
    01-Feb-2017 (TextBox 2)
    Result: 0.1 (Textbox 3)

    Thank you in advance.

  103. Moxie says:

    Hello,
    I need to create an elapsed days formula from a start date to today's date for submittals. When the submittal is returned I would like the formula to show elapsed days from the start date to the end date (received) and not today's date.
    Kind regards.

  104. Jenny Thong says:

    Hi, I'm trying to find a formula calculating days that has passed since..
    For instance, how many days have passed (aged) since date of entry. This will be updated based on an input calendar date.

    Is this possible?

  105. Hanni says:

    hello
    I'm trying to figure out, how to calculate no. days between two dates for a particular product which is repeating at different dates.

    plz help me out..
    thanks.

  106. Rab Nawaz says:

    How Two or Five Period of Years, Month, Day Example:
    01 September 1994 31 October 1994 0Years, 1Months, 30days
    04 September 1995 30 November 1995 0Years, 2Months, 26days
    01 December 1995 30 May 1996 0Years, 5Months, 29days

    Total: ______ _______ _____

  107. Gib says:

    Hello Svetlana, Good day

    I'm trying to calculate date difference is days, hours and minutes so there are 2 dates start and end date. Start Date (Date Value) and End Date (Blank or have formula). Any support would be greatly appreciated.

  108. Andy says:

    Dear,

    Really, it is very help full because sometimes if we don't get in touch with these formulation, can be washed from brain.

  109. Ashraf says:

    How to calculate number of days for the below dates
    09/04/2017 12:50 20/04/2017 11:55

    and as soon as the date cross the time of the start date, one day should be added, how to do it?

  110. Shivansh says:

    how to find the difference in dates in months so that lets say employees joining before 15th of the month are considered to be working for 1 extra month and employees joining after 15th are not considered? Is it feasible?

  111. imtiaz ahmed says:

    I need between date different by this below format

    Start Date 19.02.1989
    End Date 01.02.2017

  112. Neha says:

    Hi I am trying to automatically get column names in excel basis start date and duration as input. For example if I fill start date as 1-Jan-16 and Duration as 3 years or 36months it should automatically populate columns like Jan-16, Feb-16, Mar-16 .......Dec-18.

  113. Lisa M says:

    Great instructions! on negative dates code for between two dates! Best on the web.:) Thank you!

  114. Mahesh K says:

    Hi,
    Need a help. I want to calculate the no. of days between two dates by considering that only "sunday" is weekly off & rest 6 days are working.

    Pl advice.

  115. Samee says:

    These formulas are not working in my PC, I am using Excel 2013, and already checked all formulas are correct, surprised to see the error #value

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard