Results 1 to 3 of 3

Thread: Age in days in understandable text

  1. #1

    Age in days in understandable text

    Register for a FREE account, and/
    or Log in to avoid these ads!


    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.

    col = INT([Age]/365.2425)&"y; " & INT(([Age]-INT([Age]/365.2425)*365.2425)/30.44)&"m; " &INT([Age]-INT([Age]/365.2425)*365.2425-INT(([Age]-INT([Age]/365.2425)*365.2425)/30.44)*30.44)&"d"
    Thank you for our time.

  2. #2
    Neophyte Bernie's Avatar
    Join Date
    Feb 2018
    Suburban NY
    Excel Version
    Excel 2016 (Win)
    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 by Bernie; 2018-02-13 at 11:09 PM.

  3. #3

    Cool Thank you

    Quote Originally Posted by Bernie View Post
    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
    Thank you for your response @Bernie. I think Excel will crash if I put my birthday in.

    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 ...

    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"}})
    #"Renamed Columns1"

    Kind regards

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts