Calculate Years, Months, Days elapsed from a certain date in Microsoft Excel





In order to calculate the time elapsed from a certain date, we will use a birthdate as an example

Enter birthday date in cell A1: 01/01/1980
To calculate the number of years from the date, enter the following formula in cell B1: =YEAR(TODAY())-YEAR(A1)

The result (rounded): 23 Years

For a decimal solution, use the following formula: =DATEDIF(A1,TODAY(),”m”)/12

The result: 23.25 Years
To calculate the number of months from the date, enter the following formula in cell D1: =DATEDIF(A1,TODAY(),”m”)

The result: 279 Months

To calculate the number of days from the date, enter the following formula in cell E1: =DATEDIF(A1,TODAY(),”d”)

The result: 8499 Days

 

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.



8 thoughts on “Calculate Years, Months, Days elapsed from a certain date in Microsoft Excel

  1. How can calculate year &month in continus next row. ie.

    dec.13 =30 years
    jan.14 =30 years 1 Month
    feb.14 =30 years 2 month
    asnd so on upto the age of 65 years.
    pls. reply on my mail.

    i will be very thankfull to you.

  2. Dear Sir Pls Tell me that how to find battery life in Excel format……?

    Exam….
    Date of Sale is – 28/05/2011
    Date od Complaint is – 03/05/2014
    ————————————-
    Total used Warranty = ? / ? / ?
    ————————————-

  3. I work in human resources and I am trying to keep track of how many years my employees have been with us at any given time. I have not been able to figure out how to get the elapsed years between the two dates. I have manually done the DATEDIF function, and yes it gives me the desired answer but if I change the current date, the amount of years does not automatically update. I am getting really frustrated so please help. Also if you can reply by email that would be wonderful. Thank you so much.

  4. I work in a school setting in special education and each child must have a new evaluation every three years.

    I need a formula to count down the remaining time between evaluations and that would show the evaluations already done from a given date as a negative number or different color or something. Results need to be in a year/month/day or year.month.day format.

  5. sir i have an problem with my work.i want to send my work by automatically. if we do not done today work so how can i send it to next day.

  6. I’m trying to calculate three date ranges so I used the following formula but it is off by 1.5 years:

    =INT(((B4-A4)+(B5-A5)+(B6-A6))/365.25) & ” years, “& INT(MOD((B4-A4)+(B5-A5)+(B6-A6)/365.25,1)*12) & ” months and “&INT(MOD((B4-A4)+(B5-A5)+(B6-A6)/30.4375,1)*30.4375) &” days”

    Any suggestion?

  7. I am trying to put in a conditional format that shows red when 3 years is up on a date already in the spreadsheet. Can you help?

Leave a Reply

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


9 − one =

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>