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

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

Karel surely -2 should be the 30 Dec 1899 and day 60 should be 01/03/1900 ? Regards 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

There are two simple solutions to this, and both make far more sense for a variety of reasons beyond the built-in limitations, than using any of the standardised date formats: 1. Enter dates as numbers, starting with the year, viz: 15000627 or 20110627 2. Use 3 columns for dates, entered as numbers, one each for: YYYY MM DD Delia Robertson On 2011/06/24 02:18 AM, Keith Meintjes wrote: > 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 >

On 27 Jun 2011 at 6:44, DRobertson wrote: > There are two simple solutions to this, and both make far more sense for a > variety of reasons beyond the built-in limitations, than using any of the > standardised date formats: > > 1. Enter dates as numbers, starting with the year, viz: 15000627 or > 20110627 > 2. Use 3 columns for dates, entered as numbers, one each for: YYYY MM DD That is what I do when using spreadsheets or ordinary database files, because most database software cannot parse partial dates. For readability I usually use 1923-03-23, and it copes quite well with 1923- 03-00 or 1923-00-00 Separate columns also works, of course, and makes it easiet to select date ranges. -- Steve Hayes E-mail: shayes@dunelm.org.uk Web: http://hayesstw.tumblr.com/ (follow me on Tumblr) Blog: http://khanya.wordpress.com Phone: 083-342-3563 or 012-333-6727 Fax: 086-548-2525

On 27 Jun 2011, at 2:44 PM, DRobertson wrote: > There are two simple solutions to this, and both make far more > sense for > a variety of reasons beyond the built-in limitations, than using > any of > the standardised date formats: > > 1. Enter dates as numbers, starting with the year, viz: 15000627 or > 20110627 > 2. Use 3 columns for dates, entered as numbers, one each for: > YYYY MM DD > > Delia Robertson Makes sense. It is also the military method of denoting dates in operational communications, though they have two further wrinkles: 1. Three more fields for HH, MM, SS, and 2. They never stipulate midnight, because of the possible confusion as to the date (on the basis that it could be the midnight concluding the day or the one beginning it). Instead, they always say either 2359 or 0001, putting the matter beyond doubt. I suppose they could say 2400 or 0000, but then very few military operations are criitical to the exact second, so the one-second difference either way doesn't make an operational difference. BTW, have you noticed how many people say "12 p.m.", meaning noon? It's quite wrong, the M stands for "meridiem" (noon), which is neither before noon nor after it, so one should say "noon" or "1200" (indeed, one could argue that both a.m. and p.m. could mean midnight, the one before or after, respectively). Likewise people will say on a Tuesday that something will happen "this Thursday" -- by which, of course, they mean "next Thursday". You can only say "this Thursday" if today is Thursday. but would do this for emphasis on the fact that it is Thursday, otherwise "today" is fine. And in SA (which point may just keep this from being off-topic!) they often say "Ne-e-e-ext Thursday" when it is not yet Thursday this week and they mean Thursday next week. Don't you just love pedantry as a sport? Andrew Rodger rodgera@audioio.com