4

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.

Improve this question
5
  • 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
    Commented Mar 7, 2013 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
    Commented Mar 7, 2013 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
    Commented Mar 7, 2013 at 22:02
  • Using 4-digit year in Excel 2007
    – Brian Daniels
    Commented Mar 7, 2013 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
    Commented Mar 7, 2013 at 22:07

1 Answer 1

Reset to default
2

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

You must log in to answer this question.

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