I was trying to write a formula to find the number of days between two dates in Excel 2003. I decided to test it with the dates of the beginning and ending of the Revolutionary War. I got a #Value error.
Experimentation showed me that Excel; does not recognize any date before 1900 as a date.
Why?
Is Microsoft so business-oriented that it ignores scientific or historical uses of dates?
How do you use dates before 1900?
do you have the latest patch for office 2003
show me an example of what you are trying to acheive. i am using office 2007 and i can type in any date in any formay eg: if i want to type december 4, 1600 it come out just as that 04/12/1600 or with the dash same thing. i beleive you have to update excel wite the date patch,
and i realized you are an excell teacher, check this site out pretty awesome stuff
http://www.mrexcel.com/archive/Dates/index.html
I should have all of the updates.
Cell: contents
C2: 7/4/1776
C3: 7/8/1783
C4: =C3-C2
If you put modern dates in, it calculates the number of days between dates. If you put in dates before 1900, you get a #VALUE error.
I went to the site, and they said, "According to Excel, the world began on January 1, 1900." The date in the cell is not changed into a Julian number if it is before that date.
I think I understand why Microsoft didn't delve into dates prior to 1900.
Before 1800, there were several different calendars operating simultaneously in the Western world:
- The original Julian calendar was in use from the time of Julius Caesar (and modified by Augustus Caesar) until around 1800.
- In the 1500s, astronomers noticed that the equinoxes and solstices were 10 days out of sync with the dates they were supposed to occur on. This is because the year is not exactly 365.25 days. The Catholic Church under Pope Gregory decided to make a revision of the calendar, because the misplaced equinoxes were affecting the date of Easter.
- The result was the Gregorian calendar (which is the calendar we use today). Most Catholic countries adopted it in 1582. when 10 days were omitted from the month of September to bring the calendar back in line. The calendar also omits leap years in years that are even multiples of 100, but are not multiples of 400. This calendar is accurate to one day in 3300 years.
- Different European countries made the change in different years, so the dates in these countries would have to be figured differently.
- Great Britain and its colonies adopted the Gregorian calendar in 1752, when 11 days were removed from September (the error had increased another day over the 170 years since the Gregorian calendar started). But it took a while for the word to spread (and for people in remote regions to believe it). The old calendar finally disappeared in these countries about 1800.
- The Eastern Orthodox church and countries aligned with it continued to use the Julian calendar until 1917. They then developed with the Orthodox Calendar. It has a different leap-year-omitting scheme, and is accurate to 4200 years. But they did not shift the calendar back 12 days, electing to move the dates of the equinoxes and solstices instead. Their calendar is now 13 days behind ours (we had a leap year in 2000, but they did not).
Excel can do the Japanese and Muslim calendars, but not the Eastern Orthodox, Chinese, or Jewish calendars.
I tried this and it work for me, thiw will tell the amout of years and day between the 2 dats
If you dates are in cells A1 and B1 then:
=INT((B1-A1)/365)&" years"&" + "&(((B1-A1)/365)-INT((B1-A1)/365))*365&" days"
That will not work here, because the expression B1-A1 will return a #VALUE error if the dates are before 1900.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.