Forum Discussion

ChrisRyan7's avatar
ChrisRyan7
Copper Contributor
Jan 27, 2020

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, 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. 

excel
Formulas and Functions

3 Replies

    • ChrisRyan7's avatar
      ChrisRyan7
      Copper Contributor
      Jan 28, 2020

      SergeiBaklan Oh sheesh, yeah, that would definitely help. I edited my original post and thanks so much for letting me know.

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Jan 28, 2020

        ChrisRyan7 

        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.

Resources