Calculating Number of Days, Weeks, Months and Years between Dates in Microsoft Excel

Follow by Email
Facebook
Facebook
Google+
http://www.exceltip.com/excel-date-time-formulas/calculating-number-of-days-weeks-months-and-years-between-dates.html">
SHARE




In this article you’ll learn, how to calculate number of days, weeks, months and years between 2 dates in Microsoft Excel.

To calculate the same, we’ll use INT, TODAY and MOD or we can use DATEDIF functions.

  • INT function, will get the whole number without decimal
  • MOD function, will divide the number by a divisor
  • TODAY function, will help us get the current date
  • DATEDIF function, will calculate difference between each pair of dates

Let’s take an example,

We have 2 dates,

  • Cell A1 containing 1st date and
  • Cell A2 containing 2nd date

To calculate the difference between years, use DATEDIF function as shown in the following formula:

  • Select the cell A3 and write the formula =DATEDIF (A1,A2,”Y”)
  • This function will return the value in years

image 1

                        

To calculate the difference in months, use the DATEDIF function as shown in the following formula:

  • Select the cell A4 and write the formula =DATEDIF (A1,A2,”M”)
  • This function will return the value in months

image 2

 

To calculate the difference between days, use the DATEDIF function as shown in the following formula:

  • Select the cell A5 and write the formula =DATEDIF (A1,A2,”D”)
  • This function will return the value in days

image 3

 

OR

Use the “YEAR”, “MONTH”, “AND” and “DAY” functions as shown in the following formula:-

  • Select the cell A3 and write the formula to calculate the years
  • =YEAR(A2)-YEAR(A1)-(MONTH(A2)/12)
  • This function will return the no. of years in between 2 dates

image 4

 

Use the DATEDIF function to calculate the number of days over years:-

  • Select the cell A4 and write the formula to calculate the years
  • =DATEDIF(A1,A2,”y”)
  • This function will return the no. of years in between 2 dates

image 5

 

PS: A lot of site, avoid calculating date in Excel using DATEDIF function. The reason is “bugs”. DATEDIF functions don’t have any documentation in Excel Help file.

But, Microsoft is continuously implying this feature / formula in all new version.

In case if you also want to avoid DATEDIF function, you can use manual calculation. Like below,

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”.

image 6

 

It will give day difference in Year Month and in days. You can use A2 in case of today, where A2 is the greater day that A1 and gives you Elapsed time between these 2 dates.

 

image 48

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at [email protected]

 
 

Please follow and like us:
39


29 thoughts on “Calculating Number of Days, Weeks, Months and Years between Dates in Microsoft Excel

  1. Hi there,
    i copy-paste your formula as you written above:
    =INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”

    but it didnt work out. could you give me the correct formula for the result to count years, month, weeks, and days?

    appreciate your help.

    • Milla,

      I think you adding “&” function with space may that could be reason for error, could you pls use below and let us know

      =INT((TODAY()-A1)/365.25)&” years , “&INT(MOD((TODAY()-A1)/365.25,1)*12) &” months and “&INT(MOD((TODAY()-A1)/30.4375,1)*30.4375)&” days only”

      thanks Rishi

  2. INT((TODAY()-A1/365.25) & “years, ” & INT(MOD((TODAY()-A1/365.25,1)*12) &” moths and “& INT(MOD((TODAY()-A1/30.4375,1)*30.4375) &”days only” , i used this formula but don’t calculate, plz give me a reply

    • =INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”

      Use this. I retyped the quotation marks and it worked.

  3. I think part of the problem is trying to put everything into one cell. I split it into three cells and it works fine.

    A3: =INT((TODAY()-A1)/365.25)
    A4: =INT(MOD((TODAY()-A1)/365.25,1)*12)
    A5: =INT(MOD((TODAY()-A1)/30.4375,1)*30.4375)

  4. i’M TRYING TO SEE IF YOU CAN MAKE A FOMULA WITH A START DATE TO CALCULATE YEAR MONTH DAYS AND WILL STOP WHEN YOU ADD A STOP DATE. COULD YOU PLESE HELP ME WITH THIS PROBLEM. THANK YOU

  5. I am trying to calculate number of open days on action items. Cells used for calculation are today’s date, assigned date, target due date, date closed.

    If there is no date closed and the action item is still open, I would like to know based on today’s date how many days has the action item been open since the assigned date but if the action item has a date closed then I would like to calculate days open based on assigned date and date closed.

  6. hello,
    I have one date in a cell and in a blank cell i would like to know the number of days between that date and todays dates (that the workbook is open) is that possible?

  7. I am trying to calculate the number of months for a project end date occurring in 2014. The project start AND end date can be <2014 or greater than = 2015 – If so, the result is "0". How can I calculate the number of months in 2014 for projects that started before or during 2014?
    So far I have the following:
    =IF(OR(YEAR(End Date field )=2015),””,IF(YEAR(End Date field)>=2015,12,MONTH(End Date field)))

  8. There seems to be one thing missing from all the formulas above which is how to calculate the weeks. I need to state the months, weeks and days between two dates on my invoice. Can anybody shed some light on getting that specific formula?

  9. Can any one tell me how i can calculate Days, Months & Year from a figure like 45. Assuming month is always of 30 days. I want result like this: 0 Years, 1 Month 15 Days.

  10. Dear All,

    I hope if someone here can help me with a formula where I want to get how many days it is due. I want to subtract due dates with the current day and keeps on updating or incrementing with 1 on the next day. And, using Excel 2013

    Thanks to all who can help me with this in advance :)

    Regards,
    Flava

  11. How can I calculate the multiple months & years: -
    S. No Company From To Duration
    1 Current Com. Sep-11 Mar-15 3 years, 6 months
    2 Previous 1 Apr-10 May-11 1 years, 1 months
    3 Previous 2 Jul-09 Dec-09 0 years, 5 months
    4 Previous 3 Jun-08 Jul-09 1 years, 1 months

    Total Experience ?

  12. Hi im trying to figure out a date of birth and subtract date x to = × months as a formulae any ideas

Leave a Reply

Your email address will not be published. Required fields are marked *


× 6 = twelve

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>