How to extract an end date from a text string in a cell with a date range?

Rashaun G

Hello,

I am trying to extract an end date from a text string into a separate date cell in a separate date column. For example, in a cell with the following text string "1/1/14-6/30/22; 10,000,000 print and secure electronic" I would like to extract the end date of 6/30/22 into an End Date column cell for that row. Each row will have different end dates in a text string.

I found a formula that extracts a date in the forum and modified it with the name of the column to extract from and changed to "@row" as follows:

=IF(FIND("/", [Text Permissions Parameters]@row) = 2, DATE(VALUE(MID([Text Permissions Parameters]@row, 6, 4)), VALUE(MID([Text Permissions Parameters]@row, 1, 1)), VALUE(MID([Text Permissions Parameters]@row, 3, 2))), DATE(VALUE(MID([Text Permissions Parameters]@row, 7, 4)), VALUE(MID([Text Permissions Parameters]@row, 1, 2)), VALUE(MID([Text Permissions Parameters]@row, 4, 2))))

I can get it work for a text string with only one date but, cannot figure out how to get it to work when there is a date range (e.g., 1/1/14-6/30/22). The vast majority of the dates in the text strings has a range and I simply need to extract the end date.

Is this even possible?

Thanks in advance for any help.

Paul Newcome

Try something like this...

=DATE(VALUE("20" + MID(SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) + 1) + 1, 2)), VALUE(LEFT(SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) - 1)), VALUE(MID(SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) + 1, FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) + 1) - (FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) + 1))))

Paul Newcome

Try something like this...

=DATE(VALUE("20" + MID(SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) + 1) + 1, 2)), VALUE(LEFT(SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) - 1)), VALUE(MID(SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) + 1, FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), ""), FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) + 1) - (FIND("/", SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")) + 1))))

Rashaun G

Hello Paul,

Thank you very much. That formula worked flawlessly. I have to admit, when I first saw it, I was stunned and in awe. I would have never been able to develop that one through the trial and error tack I took, I wasn't even close.

Many thanks again

Paul Newcome

Happy to help. 👍️

I was able to piece it together by breaking it apart. I knew I could use MID/FIND and LEFT/FIND to pull the appropriate numbers form the date, but first we needed to get rid of the first date.

Enter SUBSTITUTE/LEFT:

SUBSTITUTE([String Column]@row, LEFT([String Column]@row, FIND("-", [String Column]@row)), "")

We go all the way to the hyphen and remove it. That leaves the second date as our leftmost portion of the text string

From there we can use the LEFT function to pull the month number using a FIND - 1 based on the first / and wrap it in a VALUE function to output a number.

VALUE(LEFT(substitute_formula, FIND("/", substitute_formula) - 1))

The day number uses a VALUE/MID combo using the location of the second / and subtracting from it the location of the first digit of the day number:

VALUE(MID(substitute_formula, FIND("/", substitute_formula) + 1, FIND("/", substitute_formula, FIND("/", substitute_formula) + 1) - (FIND("/", substitute_formula) + 1)))

Then for the year we use a MID to start at the digit after the second / and pull 2 digits. Add "20" to the start of the MID string and wrap in a VALUE function to produce the 4 digit year.

VALUE("20" + MID(substitute_formula, FIND("/", substitute_formula, FIND("/", substitute_formula) + 1) + 1, 2))

Now that we have the numbers, we drop them into the DATE function:

=DATE(year_formula, month_formula, day formula)

So we can take that first SUBSTITUTE formula and drop it into each of the number formulas. Then we can take those number formulas and drop them into the date formulas.

Working complex formulas one piece at a time really makes things a lot easier to manage and build.

Rashaun G

Thank you so much Paul for this explanation which is really a great tutorial (for those of us that are novice formula users). I literally just saw it when I checked back into my Community Discussions.

Thanks again and sorry for the acknowledgment delay.

Paul Newcome

Happy to help. 👍️