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

*In this article you’ll learn, how to calculate years, month, and days elapsed from a certain date in Microsoft Excel.*

*To calculate year, months and days difference between two dates we can use DATEDIF function or we can use INT, MOD and TODAY functions.*

Let’s take birthdate as an example,

**Enter birthday date in cell A2:** 8/7/1984

To calculate the number of years from the date, enter the following formula in cell B2: =YEAR(TODAY())-YEAR(A2)

**The result (rounded): 31 Years**

** **

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

**The result: 31.00 Years**

**To calculate the number of** months **from the date, enter the following formula in cell D2:** =DATEDIF(A2,TODAY(),”m”)

**The result:** 372 Months

** **

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

**The result:** 11333 Days

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

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

In case you wish to avoid DATEDIF function, you can use manual age calculation in Excel, like below:

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

It will give a day’s 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.

This will work like an age calculator.

Note: – You can see the function has returned the number of years, month and days in between two dates. You can use this formula in 2007 and later versions of Microsoft Excel.

*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 info@exceltip.com*

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

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.

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 = ? / ? / ?

————————————-

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.

=INT((TODAY()-A1)/365.25) & ” Yrs , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” Mths”

N PUT THIS FORMULA IN A2..IF YOUR JOINED DATE IS IN A1 (SO EVERYTIME THE JOINED DATE CHANGE THE ANSWER IN CELL A2 CHANGE.

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.

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.

How can i do if i want: input Date In Cell A1 and then in Cell A2 increase month every month .

Ex: Cell A1 : 25-7-2014

and Cell A2 : 25-8-2014 and one month ago

Cell A2 : 25-8-2014

Thank if can please sent to my email

Keomoyura2011@Gmail.com

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?

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?

So, I keep trying to use this formula to give me “years, months, and days”; it is giving me nothing but errors (#NAME?) . I even tried to emulate your exact numbers and not working. Is there something I’m missing? Did the formula on here have extra spaces? This is a feature I would absolutely love to be able to use for my company.

Try this formula, it actually works. S104 is the date you are counting from, you can replace the (TODAY()) Reference with another cell containing a date. As mentioned there are issues with the DATEDIF formula, missing days etc, but is accurate for most uses.

=DATEDIF(S104,(TODAY()),”y”) & ” years, ” & DATEDIF(S104,(TODAY()),”ym”) & ” months, ” & DATEDIF(S104,(TODAY()),”md”) & ” days”

Thanks mike for share this formula

Hi, I’m trying to perform what should be quite a simple function in Excel.

I have 3 columns –

Invoice Date : Payment Terms (Days) : Payment Due

The values of these columns are -

05/01/15 : 30 : ???

How do i work out what the payment due date is in excel via a formula?

Would love some help here – struggling to create the right formulas for this one – I’m quite a basic user and this uses some differing numerical formats.

Many thanks!

Hi,

Please post your query @ http://www.excelforum.com, you will get appropriate reply immediately.

Thanks

Hi!

Thank you for the article! In a range of dates I need to know how many days there are in one month and how many days in the following month!

Example:

30/05/2015-13/06/2015 : 2 days in May and 12 days in June.

Is it possible? Thank you

Oh YEAH! Well…

Birth Date: June 10, 2000

Age at the date of: December 7, 2015

Age: 15 years, 5 months and 27 days

Birth Date: January 15, 1988

Age at the date of: December 7, 2015

Age: 27 years. 10 months and 22 days

If i have a number of days available and if i have to know the date of joining of the employee how do i calculate in reverse please help

Hi,

Can any help on this:

If we have 2 columns : previous experience and current exp. than how can we add the values.

Example: My previous experience is 2.11 Years and my current Experience is 3.10 Years

Look-out!

Formula “=INT((TODAY()-A3)/365.25) & ” years , ” & INT(MOD((TODAY()-A4)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A4)/30.4375,1)*30.4375) & ” days” ” is not the same like formula:

=DATEDIF(S104,(TODAY()),”y”) & ” years, ” & DATEDIF(S104,(TODAY()),”ym”) & ” months, ” & DATEDIF(S104,(TODAY()),”md”) & ” days”

especially in leap-years.

See how it works on dates:

2016-02-01 and 2016-03-02

and

2017-02-01 and 2017-03-02