Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

up vote 2 down vote accepted

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.)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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