pre-1900 dates

Copper Contributor

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 March 30, 1894.  It calls is YYYY = 3794.  And subtracting death date from birth date returns "#VALUE!" 

 

How do I use the DATE(YYYY,MM,DD) function or format March 30, 1894 so Excel will understand and I can have it calculate the age at death correctly?

 

TIA.

 

Also I've seen #1 shown in Excel Help in different posts as BOTH Dec 31, 1899 AND as Jan 1, 1900.  Which is it?

13 Replies

John,

 

I can offer you formula #1 on excelformeln.de:

Difference of dates before 1900 / a.c.n.

@John Albertini Sounds interesting!

 

As we know Excel doesn't recognize date before 1900, but here I have a solution for you, it works perfectly!. using the formula you can calculate any date even year 1!.

 

It's not original from me, I found it here http://tips-rumus-excel.blogspot.co.id/2015/01/dates-before-1900.html but that's Indonesian version, so I modify some of the formula to English Version (such as "Year" in Indonesian is "Tahun" and so on.

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.

A very simple solution would be to just add an offset to both of the dates to be compared, so that a difference can then be calculated using the given Excel functions which start after 1900.  2000 years would include the repeating leap year dates, so should be a good value to offset.

 

Thus, 9/14/1752 12:00 am (date of Gregorian calendar acceptance) would be entered as 9/14/3752.

1/6/1980 12:00 am (0 time reference for GPS) would be entered as 1/6/3980.

The difference can then be calculated straightforward in Excel using the DAYS function, or just subtracting the earlier from the later date. (=83020 days, or 227.31x years at 365.24 days/yr). 

@Sergei Baklan 

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

PQ dates.png

Thanks

Nabil

@molokevcat 

Hi

How do you originally add any number to a date that is perceived as text?

It returns a #Value error

Please explain

Thanks

Nabil Mourad

@nabilmourad 

To interpret the number that is entered as text (for pre-1900 dates) I assume you would have to use the text separator functions using the "/" symbols to separate the YYYY/MM/DD into individual elements YYYY, MM, DD.  Then add 2000 to the pre-1900 YYYY to make it readable by Excel DATE functions.  Then concatenate back to YYYY/MM/DD in the time shifted date. Then if want to do a comparison to other dates, could just offset the newer date +2000 years also before comparing them. 

@nabilmourad 

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

image.png

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.

@Sergei Baklan 

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

@nabilmourad 

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.

Splendid, picking up on the 2000 year recurrence of the Leap Year dates cycle.
Thank you.
Is the days/year actually closer to 365.24 than .25? Will check that it out.
Recurrence is 400 years.