Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating a date
Thanks for the answers, that xdate function works perfect for me.
Regards, Hugo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating date and placing the value in the right date range col | Excel Discussion (Misc queries) | |||
Calculating the end date? | Excel Worksheet Functions | |||
Calculating Difference Between Start Date & Time And End Date & Ti | Excel Discussion (Misc queries) | |||
Calculating recurring date in following month, calculating # days in that period | Excel Worksheet Functions | |||
Calculating days between current date and a date in future NETWORKDAYS() function | Excel Worksheet Functions |