Jan 27 2020 01:14 PM - edited Jan 28 2020 04:47 AM
I'm trying to create a spreadsheet to user for genealogy research that can calculate the earliest possible day an event could have happened and the last possible day it could have happened. Basically, if the census taken on 03/01/1895 says Joe Schmoe was 53, he could have turned 53 as early as 03/02/41 and as late as 03/01/1842. The spreadsheet attached includes the target answers for what I'm looking for, but I have no idea how to get there.
Jan 28 2020 04:48 AM
@Sergei Baklan Oh sheesh, yeah, that would definitely help. I edited my original post and thanks so much for letting me know.
Jan 28 2020 05:34 AM
Thank you for the update.
First, Excel doesn't work with dates before Jan 01, 1900 (on Windows), with earlier dates we work as with texts which represents the dates. If you have mix of 20th century and earlier dates it'll be safe to transform them all to texts first, i.e. warp by TEXT function as TEXT(C3,"mm/dd/yyyy") if in C3 any date or text as the date.
With that formulas could be
Min birth date
=LEFT(TEXT([@[Date of Event to Calculate From]],"mm/dd/yyyy"),3) &
TEXT(MID( TEXT([@[Date of Event to Calculate From]],"mm/dd/yyyy"),4,2)+1,"00") &
"/" & (RIGHT( TEXT([@[Date of Event to Calculate From]],"mm/dd/yyyy"),4) -
[@Age]-1)
Max birth date
=LEFT( TEXT([@[Date of Event to Calculate From]],"mm/dd/yyyy"),6) &
(RIGHT( TEXT([@[Date of Event to Calculate From]],"mm/dd/yyyy"),4) -
[@Age])
Please check in attached file.