Age in days in understandable text

Chelena

Chelena

New member
Joined
Feb 10, 2018
Messages
3
Reaction score
0
Points
0
Location
South Africa
Excel Version(s)
365
Hi

I work with pediatric data and have the age in days that I derived from Add column --> Date --> Age. I want to convert that age in days (766) to this:
"2y; 1m; 4d". I use it repeatedly.


The DAX formula works but I would like to use it in PQ as a custom column in M please. I tried replacing INT to Int64 but no luck.

Code: 
[FONT=arial]col = INT([Age]/365.2425)&"y; " & INT(([Age]-INT([Age]/365.2425)[/FONT][FONT=arial]*365.2425)/30.44)&"m; " &INT([Age]-INT([Age]/365.2425)[/FONT][FONT=arial]*365.2425-INT(([Age]-INT([Age][/FONT][FONT=arial]/365.2425)*365.2425)/30.44)*[/FONT][FONT=arial]30.44)&"d"


[/FONT]

Thank you for our time.
 
Use this formula instead, which will compensate for different month lengths and year lengths:

=DATEDIF(TODAY()-[Age],TODAY(),"Y")&"y; "&DATEDIF(TODAY()-[Age],TODAY(),"YM")&"m; "&DATEDIF(TODAY()-[Age],TODAY(),"MD")&"d"

I am 20974 days old today ;)
 
Last edited:
Thank you

Bernie said:
Use this formula instead, which will compensate for different month lengths and year lengths:

=DATEDIF(TODAY()-[Age],TODAY(),"Y")&"y; "&DATEDIF(TODAY()-[Age],TODAY(),"YM")&"m; "&DATEDIF(TODAY()-[Age],TODAY(),"MD")&"d"

I am 20974 days old today ;)
Click to expand...

Thank you for your response @Bernie. I think Excel will crash if I put my birthday in. :Cry:

I will definitely use your formula when I need a natural age. For statistical purposes we use similar months to work out duration from one event to another, and age at event. The (.2425) after 365 and (.44) after 30 compensates for leap years in the long run.

In Power Query I sat and work it out step by step using "Divide Integer" and "Modulo" from the standard calculator. I'm from a nursing background and not too familiar with functions and measures. However, it works ...

let
Source = Excel.CurrentWorkbook(){[Name="AgeText"]}[Content],
#"Inserted Integer-Division" = Table.AddColumn(Source, "Inserted Integer-Division", each Number.IntegerDivide([Age], 365.2425), Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Inserted Modulo", each Number.Mod([Age], 365.2425), type number),
#"Inserted Integer-Division1" = Table.AddColumn(#"Inserted Modulo", "Inserted Integer-Division.1", each Number.IntegerDivide([Inserted Modulo], 30.44), Int64.Type),
#"Inserted Modulo1" = Table.AddColumn(#"Inserted Integer-Division1", "Inserted Modulo.1", each Number.Mod([Inserted Modulo], 30.44), type number),
AgeWholeNumber = Table.TransformColumnTypes(#"Inserted Modulo1",{{"Inserted Modulo.1", Int64.Type}}),
#"Removed Columns" = Table.RemoveColumns(AgeWholeNumber,{"Inserted Modulo"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Inserted Integer-Division", "Years"}, {"Inserted Integer-Division.1", "Months"}, {"Inserted Modulo.1", "Days"}}),
#"Added Suffix" = Table.TransformColumns(#"Renamed Columns", {{"Years", each Text.From(_, "en-ZA") & "y; ", type text}}),
#"Added Suffix1" = Table.TransformColumns(#"Added Suffix", {{"Months", each Text.From(_, "en-ZA") & "m; ", type text}}),
#"Added Suffix2" = Table.TransformColumns(#"Added Suffix1", {{"Days", each Text.From(_, "en-ZA") & "d", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Added Suffix2",{"Years", "Months", "Days"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Changed Type1" = Table.TransformColumnTypes(#"Merged Columns",{{"Age", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type1",{{"Merged", "AgeText"}})
in
#"Renamed Columns1"

Kind regards
 
I know this is an old thread, just using them as practice.
Here's a shorter version:
Code: 
let
    Source = Excel.CurrentWorkbook(){[Name="AgeText"]}[Content],
    AddedCustom = Table.AddColumn(Source, "AgeText", each if [Age]=null then null else Text.Format("#{0}y; #{1}m; #{2}d",{Number.IntegerDivide([Age], 365.2425),Number.IntegerDivide(Number.Mod([Age], 365.2425), 30.44),Number.Round(Number.Mod(Number.Mod([Age], 365.2425), 30.44))}))
in
    AddedCustom
 
Last edited:
You must log in or register to reply here.
Back
Top