Extract Date from Sentence

DavidHoppestad

I have a description field for a product that has a start and end date in it. I am trying to extract the Start Date and End Date to put into separate fields in Month/Day/Year format.

Example Description: RHEL SVR STANDARD PHYSICAL OR VIRTUAL NODES Comment: 11471579 Start Date 08/30/2019 End Date 08/29/2020

Thanks,

David

Paul Newcome

Are you trying to extract the dates and have them put into an actual date field, or will it remain a text string? Both are possible but have different solutions.

DavidHoppestad

I would like it put into a date field so that I can set reminders off that date and do calculations on the dates. 

Paul Newcome

Ok. Will it always be "Start Date mm/dd/yyyy" or is there the potential for m/d/yy or any other variation on date, upper vs lower case, etc?

DavidHoppestad

It will always be "Start Date mm/dd/yyyy"

Paul Newcome

This will give you the Start Date:

 

=DATE(VALUE(MID([Column2]$1, FIND("Start Date", [Column Name]@row) + 17, 2)), VALUE(MID([Column Name]@row, FIND("Start Date", [Column Name]@row) + 11, 2)), VALUE(MID([Column Name]@row, FIND("Start Date", [Column Name]@row) + 14, 2)))

 

And this will give you the Finish Date:

 

=DATE(VALUE(MID([Column Name]@row, FIND("Finish Date", [Column Name]@row) + 18, 2)), VALUE(MID([Column Name]@row, FIND("Finish Date", [Column Name]@row) + 12, 2)), VALUE(MID([Column Name]@row, FIND("Finish Date", [Column Name]@row) + 15, 2)))

DavidHoppestad

Thank you this worked great!

Paul Newcome

Happy to help!