Forum Discussion
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 ...
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
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
Hi Sergei,
When I opened your file on my office 2016 it gave me a notification that it was created with a more recent version. So, that explains why I got the error column when I used PQ.
I switched to my other computer with Office 365 Pro Plus (with office insider) and everything worked just fine.
Thanks
Nabil Mourad
Nabil, my first post in this thread is back in 2017 and such data conversion definitely worked at that time. A.D. dates in Power Query are by design from scratch.
The only, perhaps conversion required dates are to be in your locale format, I don't remember. At least now it doesn't matter.
Note that if you need to determine the EXACT number of days including leap years, I don't think the macro function that subtracts YYYY-YYYY, MM-MM, DD-DD to determine a difference will do that as written, whereas the DATE function within Excel will (when both dates are converted to post 1900 dates). You could use 365.24 days as an approximate yearly equivalent, instead of 365, then truncate the value to get an integer number of days.
