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