Forum Discussion

Copper Contributor

Nov 20, 2017

pre-1900 dates

I can find no help on how to tell Excel to properly format and calculate dates before 1900. I want to calculate age at death and have birth and death dates in columns. But Excel does not recognize ...

Excel Desktop

Formulas & Functions

nabilmourad

MVP

Jul 29, 2019

SergeiBaklan

How do you do the conversion Sergei, in Power Query

I tried to do it but PQ returned an error when I changed the data type for dates prior to 1900 from Txt to Date.

I want to learn this part

Thanks

Nabil

SergeiBaklan

Diamond Contributor

Jul 29, 2019

nabilmourad

Nabil, please check attached file. It calculates number of days from given date to today

Generated script is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TextToDate = Table.TransformColumnTypes(
        Source,
            {
                {"A", type text},
                {"Date", type date}
            }
    ),
    DatesFrom = Table.AddColumn(
        TextToDate,
        "DaysToToday",
        each Number.From(Date.From(DateTime.LocalNow())-[Date]),
        Int64.Type
    ),
    BackToText = Table.TransformColumnTypes(
        DatesFrom,
        {{"Date", type text}}
    )
in
    BackToText