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 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?
I’ve created a calendar in excel that goes back all the way to 4004 BC. On the main tab it displays one month of a year. I have inserted toggle switches to change the months and the years. At the top of the page it displays different pictures according to the month selected. To the right side of the page it displays pictures of my relatives whose birthday is in the month selected, these pictures change according to the month selected. In name manager, I have programed to display the information listed on another tab called Add names. I also have a tab called Add pictures where I add the pictures to be displayed on the main tab. On the main tab when a month is selected any person’s name whose birthday is in that month appears with their age on the particular year selected. It also shows that holidays including Thanksgiving. I also have another tab that converts the Gregorian calendar to the Julian date according to the date selected. The way to force excel to recognize dates prior to Jan. 1st 1900 is to move it forward in increments of four hundred years as the years repeat exactly every four hundred years including the day of the week. I have moved mine forward 6800 years. That date is hidden and the year displayed is that date minus 6800 years. It was a little complicated to skip the zero year going back to BC dates but it is doable.
Any chance you would consider sharing a copy/template of such a file? Not looking to peruse your family tree, but what you have described sounds fascinating!
I have tried to share it with you using the share button in excel. The email I shared with is email address removed for privacy reasons. I hope that is you and I hope it works. If it doesn't work, let me know and I will try something else..
Sounds impressive.
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).
- 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.
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
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.
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 BackToText
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.
