Oops Karel ... sorry! I think I misinterpreted your inference! P ----- Original Message ----- From: Karel Marais To: south-africa@rootsweb.com Sent: Friday, June 24, 2011 9:27 AM Subject: Re: [SOUTH-AFRICA] Dates in Excel Hi, Glad to report that -2 changed to dateformat on my openoffice calc gives a date of 28 December 1899 Day 60 also formats correctly to 28/02/1900 ...might be time to get some free software :-) Cheers, Karel Marais Marais x Thurnau Lloyd x Pienaar Bennett x Cloete Webber x Maskrey Kapp x Slabbert, Bestbier Du Toit --- On Fri, 24/6/11, Keith Meintjes <umfundi@usa.net> wrote: From: Keith Meintjes <umfundi@usa.net> Subject: [SOUTH-AFRICA] Dates in Excel To: "SA List" <SOUTH-AFRICA-L@rootsweb.com> Date: Friday, 24 June, 2011, 3:18 All, May I complain a little bit? I am amazed to find the limitations and errors in Excel's default date calculations. I will have to go back and check my spreadsheets for date calculations. 1. Day 1 (on the PC) and Day 0 (on the Mac) are four years apart. If you cut and paste dates from one platform to the other, they will differ by four years and one day. To check: Make a new spreadsheet, type 1 in a cell, and then format it as a date. If the result is Jan 1, 1900 you are on the default for a PC. If the result is Jan 2, 1904, you are on the default for a Mac. 2. 1900 was not a leap year. But, Excel preserves a bug in Lotus123 that it was. So, on a PC, any day calculation before March 1, 1900, (but after Jan 1, 1900) will be in error by one day. 3. Dates before Jan 1 1900 (on the PC) and before Jan 1 1904 (on the Mac) do not exist. On the PC (1900 system) they are undefined. On the Mac, they are a negative reflection of the future! Make a new spreadsheet, type -2 in a cell, and then format it as a date. On a PC you will get an infinite string of ####, on a Mac you will get -Sunday, January 3, 1904. If you want to be a geek, you can switch the setting: Options, Advanced, Calculate, Check or uncheck the box for 1994 date system. It just blows my mind that when you copy data between spreadsheets, Excel does not check and reconcile the date system. Also, the 1900 leap year error persists? Finally, I do not care what "zero" is. But, (negative) dates before zero are wrong or undefined? Give me a break! These are simple issues to fix. Keith ------------------------------- To unsubscribe from the list, please send an email to SOUTH-AFRICA-request@rootsweb.com with the word 'unsubscribe' without the quotes in the subject and the body of the message ------------------------------- To unsubscribe from the list, please send an email to SOUTH-AFRICA-request@rootsweb.com with the word 'unsubscribe' without the quotes in the subject and the body of the message