Extract Date from Sentence
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
Comments
-
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.
-
I would like it put into a date field so that I can set reminders off that date and do calculations on the dates.
-
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?
-
It will always be "Start Date mm/dd/yyyy"
-
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)))
-
Thank you this worked great!
-
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives