If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating a date
I've discovered a pile of mortuary cards and I'm trying to put them in an
Excel file. But often is only the date of death and the age mentioned, without the date of birth. Does someone know a method in Excel to calculate that? For instance: someone who died on february 12 1850 on the age of 83 year 5 month and 15 days; what's his birth date. Thanks, Hugo |
#2
|
|||
|
|||
Calculating a date
Hugo wrote:
I've discovered a pile of mortuary cards and I'm trying to put them in an Excel file. But often is only the date of death and the age mentioned, without the date of birth. Does someone know a method in Excel to calculate that? For instance: someone who died on february 12 1850 on the age of 83 year 5 month and 15 days; what's his birth date. Thanks, Hugo Try this: http://www.j-walk.com/ss/excel/files/xdate.htm A1 = 2/12/1850 B1 = 83 C1 = 5 D1 = 15 E1 = XDATE(XDATEYEAR(A1)-B1,XDATEMONTH(A1)-C1,XDATEDAY(A1)-D1) |
#3
|
|||
|
|||
Calculating a date
The first problem is that Excel cannot work with dates before 1/1/1900
John Walkenback has a free add-in to overcome this http://www.j-walk.com/ss/excel/files/xdate.htm Even then I cannot see a function that does exactly what you need. You may need to do it in steps Let's pretend my birth date was 15/Mar/1939 (close to my actual DOB) I used DATEDIFF to find that my age would be 70 year, 9 months, 22 days. Here is my backward calculation to find my assumed DOB today 2010 1 6 my age 70 9 22 date of birth 1940 -8 -16 fix month 1939 4 -16 fix day 1939 3 15 In row 3, I used formulas like =B1-B2 to get 1940, -8,-16 In row 4, I subtracted 1 from year and added 12 to month In row 5, I subtracted 1 from month and added 31 to day I did the same with a DOB of 12/Oct/1941 and it produced the correct result It seems to work but you will need to experiment as to when to add 28, 29, 30 or 31 in the last step Maybe being a day out will not matter - how good was the calculation in the mortuary data? Problem 2 for dates before 1752: Gregorian vs Julian Calendars. See http://www.exceluser.com/explore/earlydates.htm best wishes -- Bernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme "Hugo" wrote in message ... I've discovered a pile of mortuary cards and I'm trying to put them in an Excel file. But often is only the date of death and the age mentioned, without the date of birth. Does someone know a method in Excel to calculate that? For instance: someone who died on february 12 1850 on the age of 83 year 5 month and 15 days; what's his birth date. Thanks, Hugo |
#4
|
|||
|
|||
Calculating a date
Thanks for the answers, that xdate function works perfect for me.
Regards, Hugo |
Thread Tools | |
Display Modes | |
|
|