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 ...
As a comment
- dates in Excel are actually integer numbers where 1 is equal to Jan 01, 1900 (if we speak about PC version). However, if format zero as date it will be returned as Jan 00, 1900 which could be interpreted as Dec 31, 1899.
- there are quite many techniques how to work with dates before 1900. Since such dates are interpreted as text it very depends on in which format your dates are presented since in any case you need to parse your text to substruct element of the date (year, month, day), convert to numbers and compare with another date (which is the number if after 1900 or another text). Or by adding few thousand of years to each date.
- if some dates are before shifting from Julian on Georgian calendar that also shall be taken into account adjusting the result on the days of shift.
- all above could be done in few ways
by different kind of formulas (google will provide the samples)
with using VBA. The starting point could be Microsoft sample How to calculate ages before 1/1/1900 in Excel or you may generate your own macro taking into account what built-in VBA function DateDiff works correctly with dates starting from year 100 (if dates text representation is in your locale format).
using Power Query (Get&Transform). It correctly interprets dates before 1900 if convert them from text to date. The only point of final step after you calculate durations not to forget return format back to text before loading resulting table into the Excel sheet. Power Query even recognizes the dates if the text is in long/short date formats like December 25, 1865.
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 BackToTextHi 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.
