Calculating Number of Days, Weeks, Months and Years between Dates





Problem:

Calculating the difference between each pair of dates listed in columns
A & B.

Solution:

To calculate the difference in days, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”d”)

To calculate the difference in weeks, use the INT function as shown in the following formula:
=INT((B2-A2)/7)

To calculate the difference in months, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”m”)

To calculate the difference in years, use one of the following two solutions:
Use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”y”)

OR

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

=YEAR(B2)-YEAR(A2)-(MONTH(B2) To calculate the number of months over years, use the DATEDIF function as shown in the following formula:
=DATEDIF(A2,B2,”ym”)

To calculate the number of days over years, use the DATEDIF function as shown in the following formula:

 

PS: A lot of site, avoid to calculate date in Excel using DATEDIF function, due to some of bugs, as DATEDIF function dont have any documentation in Excel Help file,

But, Microsoft continuously giving 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”

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

datedif

 

Arlette Aloysius

Arlette Aloysius has more than 10 years of experience working with excel. She started teaching excel and other microsoft office programs to government employees when she was in college as part of extra-curricular activities. In her 9 years of work experience, she has worked extensively on excel and automated several reports using vba. In her current role as administrator of Excelforum.com, she provides simple as well as complicated vba solutions to users who need their reports to be automated. She is a Commerce graduate who specialized in Computer Applications where she was able to pick up the basics of the VB programming language. She then pursued to do her Post Graduate Diploma in International Business. Excel sheets are part of her daily life. She uses it even for small personal uses like keeping track of household expenses, investments, etc. By using excel as a tracker cum dashboard helps her to be up-to-date with all financial aspects in her day to day operations.



5 thoughts on “Calculating Number of Days, Weeks, Months and Years between Dates

  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)

Leave a Reply

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


eight − = 2

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>