For Delia and Steve: I bought a spreadsheet, not a dumb table. I expect to be able to do calculations involving dates, such as: Age at death, mother's age when bearing children, etc. There are a host of age-related calculations you can (or should be able to) make to check your data. For Paxie and Karel: In Excel 2000 PC, Day 0 is "0 Jan 1900". Whatever that is. Day -2 is :"#########################################################################", it will fill the display with "#", no matter how many characters. Day 60 is "February 29, 1900", a leap year day that did not exist. What Karel tells us of Open Office is quite clever: Day 0 is "December 30, 1899". I presume they correct the 1900 leap year error, and then in Open Office the day number for dates after March 1, 1900 is the same as that in the Excel 1900 date system. Keith ------ Original Message ------ Received: Mon, 27 Jun 2011 02:30:46 AM EDT From: "Paxie Kelsey" <paxie@telkomsa.net> To: <south-africa@rootsweb.com> Subject: Re: [SOUTH-AFRICA] Dates in Excel 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 ------------------------------- 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

Keith, On 2011/06/28 06:32 AM, Keith Meintjes wrote: > For Delia and Steve: > > I bought a spreadsheet, not a dumb table. I expect to be able to do > calculations involving dates, such as: Age at death, mother's age when > bearing children, etc. There are a host of age-related calculations you can > (or should be able to) make to check your data. You bought spreadsheet sotware that never promised to do the calculations you want - there were always date limitations. What you need is a proper genealogical relational database program that automatically does these basic calculations and displays them on the individual's page without you even having to ask it; and does the more complicated ones in milliseconds with a simple popup utility. Delia

On 28 Jun 2011 at 0:32, Keith Meintjes wrote: > I bought a spreadsheet, not a dumb table. I expect to be able to do > calculations involving dates, such as: Age at death, mother's age when > bearing children, etc. There are a host of age-related calculations you can > (or should be able to) make to check your data. True, but most spreadsheets' date functions do not gom back much more than a century, so they are useless for that purpose. Database programs are better - I used Paradox at one time, where date functions worked back to AD 200, thopugh it took no account of things like changes in the calendar, but worked everything out in Gregorian. I don't know what currently available relational database programs, like Access and Base, do with dates. I mostly use Inmagic, which isn't relational, but is rather a text database, but it handles dates OK, in that it can understand and sort on partial dates, and even recognises them if you enter them in different formats. You can have 23-May-1783, or 23-5-1793 or 7/23/1793 and it will sort it correctly. It will even recognise c1793, though the program authors intended that to mean "copyright" rather than "circa", but it works. I no of no spreadsheet that can do that, and no relational database program that can do it either, unless you write a lot of scripts, which the purveyors of lineage-linked genealogy software must do. -- 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