Extracting one or more date from one cell and copying that value into a new columns

Question

I need to get 02 or more dates from a cell, and split in new columns to calculate the diference between the dates.

It always follows the same structure: "text example from 4/2024 to 1/2029".

Maybe find the "to" and get 7 characteres from left and right. Sometimes I have more than 1 "from to" expressions in the same cell.

Thank you so much!

Follow some examples (the max "from to" I can find in the same cell are 3:

Yoshiro_Fujimori · Accepted Answer

I tried to follow your expected output. I hope this helps.

I believe the Difference Months in the last row should be 23 (if "To" date is not inclusive).

Input Data

Action Descriptiontext text text from 4/2028 to 1/2029text text text from 8/2027 to 3/2028text text text from 4/2028 to 1/2029, text text text from 8/2029 to 05/2030text text text from 8/2027 to 3/2028, text text text from 1/2027 to 12/2028,

Output Data

RecordIDAction DescriptionFromToFromDateToDateDifference Months1text text text from 4/2028 to 1/20294/20281/20292028-04-012029-01-0192text text text from 8/2027 to 3/20288/20273/20282027-08-012028-03-0173text text text from 4/2028 to 1/20294/20281/20292028-04-012029-01-0193 text text text from 8/2029 to 05/20308/202905/20302029-08-012030-05-0194text text text from 8/2027 to 3/20288/20273/20282027-08-012028-03-0174 text text text from 1/2027 to 12/20281/202712/20282027-01-012028-12-0123

Workflow

Formula Tool

FromDate = DateTimeParse([From],"%m/%Y")

ToDate = DateTimeParse([To],"%m/%Y")

Difference Months = DateTimeDiff([ToDate],[FromDate],"month")