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.

  • 1
    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
  • 1
    @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

Some ideas from exceluser.com:

  • 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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.