Case Study - 1 Solution: Extracting Past Dates from a Timeline in Excel

?? Problem:

Given a current date and a timeline (e.g., "1 year 10 months 3 days"), how can we calculate the exact date before this timeline using a single Excel formula?

? Solution: We use the formula:

=EDATE(A4,-LEFT(B4,FIND(" ",B4)-1)*12-MID(B4,SEARCH("months",B4)-3,2))-MID(B4,SEARCH("days",B4)-3,2)        

Let’s break it down step by step! ??

?? Step 1: Extract the Number of Years:

LEFT(B4, FIND(" ", B4) - 1)        

?? This extracts the number of years from the timeline (before the first space).For example, in "1 years 10 months 3 days", this returns 1.

Since 1 year = 12 months, we multiply by 12

LEFT(B4, FIND(" ", B4) - 1) * 12        

So, 1 year = 12 months and 2 years = 24 months.

?? Step 2: Extract the Number of Months:

MID(B4, SEARCH("months", B4) - 3, 2)        

?? This extracts the number of months from the timeline.

• It searches for "months" in the text and takes the two characters before it (which represent the number of months).
• In "1 years 10 months 3 days", this returns 10.

?? Step 3: Calculate the Total Months

Now, we subtract both years converted to months and the extracted months from the current date:

EDATE(A4, -LEFT(B4, FIND(" ", B4) - 1) * 12 - MID(B4, SEARCH("months", B4) - 3, 2))        

?? EDATE(A4, -X) shifts the date backward by X months.

?? Step 4: Extract the Number of Days

MID(B4, SEARCH("days", B4) - 3, 2)        

?? This extracts the number of days from the timeline.

Finally, we subtract the extracted days from the date calculated in Step 3:

EDATE(...) - MID(B4, SEARCH("days", B4) - 3, 2)        

?? Step 5: Get the Final Date

Now, we get the exact past date before the timeline! ??