Forum Discussion
Calculating Birthday list with birthdays before 1900
Hello,
I am working on a birthday list and also for how old relatives were when they passed away.
Some relatives were born before 1900.
Could someone help me with a formula that would fill in these cells correctly. I would like to keep the format I have for the birthdays after 1900, and without using any type of Macros.
| Birthdate | Age (as of file opened) | Date of Death | Age of Death |
| 10/9/1895 | #VALUE! | 7-9-1896 | #VALUE! |
| 2-24-1897 | #VALUE! | Jan / 02 / 1973 | #VALUE! |
| 12-19-1898 | #VALUE! | Mar / 16 / 1903 | #VALUE! |
| Aug / 23 / 1900 | 121 Y 6 M 12 D | Feb / 12 / 1934 | 33 Y 5 M 20 D |
| Apr / 18 / 1927 | 94 Y 10 M 17 D | Jan / 13 / 2001 | 73 Y 8 M 26 D |
| Oct / 16 / 1907 | 114 Y 4 M 19 D | Nov / 26 / 2001 | 94 Y 1 M 10 D |
| Mar / 01 / 1912 | 110 Y 0 M 6 D | Apr / 07 / 1997 | 85 Y 1 M 6 D |
Thank you,
Bob
1 Reply
=DATEDIF(IF(ISTEXT(A1),LEFT(A1,LEN(A1)-4)&IF(A2="v",-1,1)*RIGHT(A1,4)*1+4000,A1),IF(ISTEXT(B1),LEFT(B1,LEN(B1)-4)&RIGHT(B1,4)*1+4000,IF(ISTEXT(A1),DATE(YEAR(B1)+4000,MONTH(B1),DAY(B1)),B1)),"Y")&" Years, "&DATEDIF(IF(ISTEXT(A1),LEFT(A1,LEN(A1)-4)&IF(A2="v",-1,1)*RIGHT(A1,4)*1+4000,A1),IF(ISTEXT(B1),LEFT(B1,LEN(B1)-4)&RIGHT(B1,4)*1+4000,IF(ISTEXT(A1),DATE(YEAR(B1)+4000,MONTH(B1),DAY(B1)),B1)),"Ym")&" Months, "&DATEDIF(IF(ISTEXT(A1),LEFT(A1,LEN(A1)-4)&IF(A2="v",-1,1)*RIGHT(A1,4)*1+4000,A1),IF(ISTEXT(B1),LEFT(B1,LEN(B1)-4)&RIGHT(B1,4)*1+4000,IF(ISTEXT(A1),DATE(YEAR(B1)+4000,MONTH(B1),DAY(B1)),B1)),"md")&" Days"
Contains array formula: Do not enter border { }, but close the formula with CTRL+SHIFT+RETURN
Awkward but effective:)
Hope I was able to help you with this info.
I know I don't know anything (Socrates)
Was the answer useful? Mark them as helpful!
This will help all forum participants.
