Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Are there any workarounds for working with time and date data prior to January 1, 1900? Excel seems to just assign my values dates in 1900 rather than keeping them intact.

share|improve this question
Can you give us some more information on how you're manipulating data? If the data is just being entered, 4-digit year seems to work for me. Are you using it in formulas or dependencies? – Brian Daniels Mar 7 '13 at 21:51
@BrianDaniels Are you using a newer version of Excel and/or dates since 1900? Excel dates are an (unsigned) offset from 1/1/1900. (Hint: try sorting them...) – GalacticCowboy Mar 7 '13 at 21:59
I'm working with dates from 0 C.E. through about 1700 C.E. Using yyyy also forces the dates to 1900. – Dan Mar 7 '13 at 22:02
Using 4-digit year in Excel 2007 – Brian Daniels Mar 7 '13 at 22:05
If you enter 1/1/1899, 1/1/1900, 1/1/1901 in Excel and then sort "oldest to newest", it puts the 1899 date last. – GalacticCowboy Mar 7 '13 at 22:07
up vote 2 down vote accepted

Some ideas from

  • Enter them as text, in YYYY-MM-DD format. This should allow sorting to work as expected.
  • Create or install a macro that converts dates before 1900 to a negative serial. (The site links one.)
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .