How to calculate the Age between two Dates Times and show it in a meaningful way

Qsn: How to calculate the Age between two Dates / Times and show it in a meaningful way?

Sol: Use the simple formula “A-B” (NewDate – OldDate) just same as we subtract two numerical values, but added with a simple trick… Know how…

I have
Old Date “May 10, 1985” in cell “B2”
New Date “Jan 01, 2014” in cell “C2”

How to calculate the Age between two Dates Times and show it in a meaningful way
How to calculate the Age between two Dates Times and show it in a meaningful way

Now the action starts…
In cell “D2” enter the formula =C2-B2
This will return a numerical value “10463” which is infact number of days between two dates, but how to make them more meaningful

>> Select cell “D2”
>> Right Click
>> Select “Format Cells”
>> Select “Custom” from Category

==>> For Age difference
>> Enter (yy “Years”, mm “Months”, dd “Days”) in “Type” space
It will convert “10463” to “28 Years, 08 Months, 23 Days”

==>> For Time difference (in cell D7)
>> Enter (hh “Hours”, mm “Minutes”, ss “Seconds”) in “Type” space

Ta.. Da…. Magic….!!

One more way to get this done is using TEXT() formula, but it is not as flexible as Format Cells option

Please Let us know if you have a better & simple solution..

Comments

    1. Raghu Ram Alla

      It is not excess/extra. But it shows current month

      For e.g: If you type both the dates as same, it will show 1 month 0 days. it means 1st month & 0 days completed. I guess this is because US date format starts with month as “MM/DD/YYYY”. Hope this helps you.

Leave a Reply to kamal bansal Cancel reply

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

5 − 1 =

This site uses Akismet to reduce spam. Learn how your comment data is processed.