dec 31, 1899

[lezawang]

Hi I know I can not enter that date 31/12/1899 in excel because the first day is 1/1/1900. What to do if I have application need to enter date in 1899? excel wont be good choice for that application for example? Thank you.

 

[Macropod]

Any date calculations in Excel that concern dates before 1 March 1900 are run into the year 1900 'leap year' bug - February 1900 was not a leap year but, for compatibility with Lotus 123, Excel replicated that application's year 1900 'leap year' bug. hence dates before 1 Jan 1900 aren't the only issue. That said, see: http://spreadsheetpage.com/index.php/tip/extended_date_functions/

 

[Jonmo1]

You can try using the 1904 date system in Excel.
File - Options - Advanced - 'When calculating this workbook'

That will allow older dates.
I'm not familiar enough with that option to know what (if any) side effects that may have. I suggest testing on a 'copy' of your original book.

 

[mikerickson]

The linked file that Macropod referenced basically substitutes VBA's date processing for the normal worksheet functions.
VBA handles dates differently that the worksheet functions. 1 is still 1 Jan 1900, but in VBA dates before that are allowed and handled correctly.
Also DateValue("2/28/1900")+1 is 3/1/1900 in VBA, but 2/29/1900 on the worksheet.

If I had to use Excel with dates before the 20th century, I'd format the cells to TEXT and use UDF's for date calculations. (Like the Macropod's linked file)