Forum Discussion
Calculating Dates in Excel
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...
Sorry, you forgot the attachment
SergeiBaklan Oh sheesh, yeah, that would definitely help. I edited my original post and thanks so much for letting me know.
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.