Question: How to calculate the Age between two Dates / Times and show it in a meaningful way?
Solution: 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"
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..
Solution: 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"
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..
0 comments:
Post a Comment