SOLVED

Date of age with fraction

%3CLINGO-SUB%20id%3D%22lingo-sub-1616233%22%20slang%3D%22en-US%22%3EDate%20of%20age%20with%20fraction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616233%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone!%3C%2FP%3E%3CP%3EI%20usually%20calculate%20the%20date%20of%20people%20turning%20different%20ages%2C%20but%20I%20struggle%20to%20calculate%20when%20ages%20are%20given%20fraction%20part.%20For%20example%2C%20I%20know%20my%20clients%20birthday%20and%20should%20find%20date%20when%20he%2Fshe%20turns%2074%2C4.%3C%2FP%3E%3CP%3EI've%20tried%20many%20functions%2C%20but%20couldn't%20find%20a%20solution.%20I%20am%20tired%20of%20calculating%20on%20paper%2C%20it%20would%20be%20great%20I%20could%20calculate%20it%20with%20the%20helpm%20of%20Excell.%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1616233%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1616328%22%20slang%3D%22en-US%22%3ERe%3A%20Date%20of%20age%20with%20fraction%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1616328%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F773325%22%20target%3D%22_blank%22%3E%40khujaabbos%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20could%20be%3A%3C%2FP%3E%3CPRE%3E%3D%5BBirthdate%5D%2B%5BDecimal_Age%5D*365.2422%3C%2FPRE%3E%3CP%3ENote%20that%20we%20account%20for%20leap%20years%20by%20using%20the%20exact%20amount%20of%20days%20per%20revolution%20around%20the%20sun%20(365.2422%20days).%3C%2FP%3E%3CP%3EYou%20can%20use%20the%20formula%3A%3C%2FP%3E%3CPRE%3E%3DYEARFRAC(%5BDATE%5D)%3C%2FPRE%3E%3CP%3ETo%20verify%20the%20decimal%20age%20is%20correct.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello everyone!

I usually calculate the date of people turning different ages, but I struggle to calculate when ages are given fraction part. For example, I know my clients birthday and should find date when he/she turns 74,4.

I've tried many functions, but couldn't find a solution. I am tired of calculating on paper, it would be great I could calculate it with the helpm of Excell.

Thanks in advance.

 

5 Replies
Highlighted

Hello @khujaabbos,

 

That could be:

=[Birthdate]+[Decimal_Age]*365.2422

Note that we account for leap years by using the exact amount of days per revolution around the sun (365.2422 days).

You can use the formula:

=YEARFRAC([DATE])

To verify the decimal age is correct.

Highlighted

@khujaabbos 

I wonder, what is the benefit of such a calculation?

 

Highlighted

@Detlef Lewin 

Average lifespan of both males and females are given in this form, and our clients will be recieving insurance benefits till this date. Therefore, I need to know when to stop them recieving insurance benefits.

Highlighted

Hello @PReagan!,

Thank you so much for your reply. Unfortunately, I didn't understand the first part of the reply. Could you show it with an example? And was it supposed to be done on Excell?

 

Thanks in advance.

Highlighted
Best Response confirmed by khujaabbos (New Contributor)
Solution

@khujaabbos 

 

[Birthday] represents a cell that contains the participants birthday. For example, B1 = 1/1/1946

[Decimal_Age] represents a cell that contain the participants age in decimal format. For example B2 = 74.4

The number 365.2422 represents the exact amount of days in a year.

Using the formula, the date in which the participant turns 74.4 is:

=B1+B2*365.2422 or 5/26/2020

PReagan_0-1598628370520.png PReagan_1-1598628448038.png

You can use YEARFRAC(B1,B3) to verify that the decimal age is correct.