Calculate date format and age after certain years

Given: Birthday is May 02, 1972, today's day is Feb. 23, 2018.

I tried to determine the current age in decimal, I use (02/23/2018 - 05/02/1972)/365 = 45.8438356 years
After 11.372 years, I add 11.372 years + 45.8438356 years = 57.2158 years

So after 11.372 years at 57,2158 years old ......
1. What will the date in MM/DD/YYYY format
2. Age in years, months, days format?
I tried do this: 57 years, 0.2158*12=2.5896 = 2 months, 0.5896*(265/12)=17.9337 days = 17 days.
I use INT to drop the numbers after the decimal places even the result is 17.9337. I think it is right but not sure if it is the
best way.

Please note birthday and 11.372 years are variable values, not a fix value.

I am looking for help using functions not VBA.


Thanks

With DOB in A2 and =TODAY() in B2, a simple formula:

Formula:

Please Login or Register  to view this content.

DOB 25/12/1925 = 92.16897744 years

OR....
The number of years (y) = (year of previous birthday) - (year of birth)
The number of days (d) = serial value(today) - serial value(day of previous birthday)
Days between birthdays (b) = serial value(next birthday) - serial value(previous birthday)
Decimal = y + (d/b)

With DOB in A2 and =TODAY() in B2

Formula:

Please Login or Register  to view this content.

DOB 25/12/1925 = 92.16712329
=INT((B2-A2)/365)+((B2-EDATE(A2,(DATEDIF(A2,B2,"y"))*12))/(EDATE(A2,(DATEDIF(A2,B2,"y")+1)*12)-EDATE(A2,(DATEDIF(A2,B2,"y"))*12)))

Explained as follows:

Years
=INT((B2-A2)/365)

Days between last birthday and next one (either 365 or 366)
=EDATE(A2,(DATEDIF(A2,B2,"y")+1)*12)-EDATE(A2,(DATEDIF(A2,B2,"y"))*12)

Days since last birthday
=B2-EDATE(A2,(DATEDIF(A2,B2,"y"))*12)

B2=Birthday
B3=Today's Date
B4=No. of years after

B5 = No.of years after*365
In B6 Use Below formula

=DATEDIF(B2,B5,"Y")&" Y " &DATEDIF(B2,B5,"YM")&" M " &DATEDIF(B2,B5,"MD")&" D"

?years, ? Months, ?Days

with DOB in A2, today's date in B2

years
=DATEDIF(A2,B2,"y")
months
=DATEDIF(A2,B2,"ym")
days
=DATEDIF(A2,B2,"md")

I think this is easy to find today's age than the formula at the top.
The result from above are 92 years, 1 month, 29 days. This is the age based on today's date of 23/02/2018.

What is the date format in dd/mm/yyyy after 11.372 year later? Note: I follow your date dd/mm/yyyy, I typical use mm/dd/yyyy.

If I use the first formula you provide =INT((B2-A2)/365)+((B2-EDATE(A2,(DATEDIF(A2,B2,"y"))*12))/(EDATE(A2,(DATEDIF(A2,B2,"y")+1)*12)-EDATE(A2,(DATEDIF(A2,B2,"y"))*12)))
your result shows today's age = 92.16712329; but I use the same formula I got 92.16438 a small difference.

To find the age after 11.372 years, I use 92.16712329 + 11.372 = 103.5391
Since your formua is calculate based on date format dd/mm/yyyy, I could not use the above to calculate the age after 11.372 years later.

I could use =MID(103.5391,1,FIND(".",103.5391,1)-1) = 103 years MID(103.5391,1,FIND(".",103.5391,1)-1) = 103
then use 0.5391*12=6.4692=6 months, then 0.4692*(365/12)=14.2715=14 days

OR
Use Phuocam's formula, =TEXT(SUMPRODUCT(DATEDIF(0,B12*365.25,{"y","ym","md"})*{10000,100,1}),"#0"" years ""00"" month ""00"" days""")

I still get stuck to find the the date format dd/mm/yyyy or mm/dd/yyyy, knowing the age is 103.5391 years old. For the year, 103-92+2018=29, so dd/mm/2029

Can you please advise the date format at age 103.5391 years old.

Thanks

@Rocky2013
You are confusing things for yourself by working in YEARS
In Excel Dates are DAYS
Excel Day 1 = 1/1/1900, Excel Day 43155 = TODAY Feb 24, 2018
(So, according to Excel, the world is 43155 DAYS old!)

Start again and calculate in DAYS

DOB 02/05/1972 (mm/dd/yyyy) = 26334
Today 02/24/2018 (mm/dd/yyyy) = 43155
Age = 43155 - 26334 = 16821 DAYS old

Do not add 11.372 YEARS
You used 365 in your calculations, so I think 11.372 X 365 = 4151 DAYS
Today + 4151 DAYS = 43155 + 4151 = 47306
47306 formatted as a date = 07/07/2029

DOB + AGE IN DAYS + VALUE ADDED
26334 + 16821 + 4151 = 47306
47306 formatted as a date = 07/07/2029

To allow cell value to be formatted as a date, the underlying cell value should be in days

Can you please advise the date format at age 103.5391 years old

No - that is not a date. It is a number of years - which not the same thing

See the attached file

Hi kelv,
Indeed I confuse myself with days and date.

After I try your new method, your method makes it very simple and it works
what I want.

Also thanks others who provide me the help. I am sorry for all the confusion make it more
difficult.


Thanks