Extract Date from Sentence

J. Angel

I have a column that is for incident dates that has the day, date, and time. I need to extract just the Date.

Screenshot:

Kelly Moore

Hey @J. Angel

I agree completely with Paul's suggestion. The data would be much cleaner if the date and time were separated.

If, however, you are unable to add columns and separate, this formula below might work for you. It is keying off the special characters you have in the text string. CHAR(44) is a comma. CHAR(47) is the forward slash. CHAR(40) is the leading parenthesis. You can find a list of those characters here

=DATE(VALUE(MID([Incident_Date]@row, FIND(CHAR(40), [Incident_Date]@row) - 5, 4)), VALUE(MID([Incident_Date]@row, FIND(CHAR(44), [Incident_Date]@row) + 2, FIND(CHAR(47), [Incident_Date]@row) - 2 - FIND(CHAR(44), [Incident_Date]@row))), VALUE(MID([Incident_Date]@row, FIND(CHAR(47), [Incident_Date]@row) + 1, FIND(CHAR(47), [Incident_Date]@row, FIND(CHAR(47), [Incident_Date]@row) + 1) - FIND(CHAR(47), [Incident_Date]@row) - 1)))

The DATE function has the syntax DATE(YEAR, MONTH, DAY). The different MID functions are parsing those three parameters into three separate text strings.

The VALUE function converts the text, even though it appears to be a number, to an actual number. The FIND function returns a position number of whatever you're looking for within the specified text string. We're able to use that number to tell the MID function where to parse from. We can also use the FIND function to determine the number of characters to return by looking for the number of characters between two special characters, such as the leading slash and the next slash that bracket the 'Day' in your text string. Does this make sense?

Again, if you can use Paul's suggestion you would end up with a formula that didn't look quite as intimidating.

cheers,

Kelly

Paul Newcome

How are new entries being made?

My suggestion would be to have the data entry placed into different columns instead of trying to parse it out after. If you use a date column for the date and a text column for the time, we can put together a pretty straightforward nested IF statement to output the day of the week.

Kelly Moore

Hey @J. Angel

I agree completely with Paul's suggestion. The data would be much cleaner if the date and time were separated.

If, however, you are unable to add columns and separate, this formula below might work for you. It is keying off the special characters you have in the text string. CHAR(44) is a comma. CHAR(47) is the forward slash. CHAR(40) is the leading parenthesis. You can find a list of those characters here

=DATE(VALUE(MID([Incident_Date]@row, FIND(CHAR(40), [Incident_Date]@row) - 5, 4)), VALUE(MID([Incident_Date]@row, FIND(CHAR(44), [Incident_Date]@row) + 2, FIND(CHAR(47), [Incident_Date]@row) - 2 - FIND(CHAR(44), [Incident_Date]@row))), VALUE(MID([Incident_Date]@row, FIND(CHAR(47), [Incident_Date]@row) + 1, FIND(CHAR(47), [Incident_Date]@row, FIND(CHAR(47), [Incident_Date]@row) + 1) - FIND(CHAR(47), [Incident_Date]@row) - 1)))

The DATE function has the syntax DATE(YEAR, MONTH, DAY). The different MID functions are parsing those three parameters into three separate text strings.

The VALUE function converts the text, even though it appears to be a number, to an actual number. The FIND function returns a position number of whatever you're looking for within the specified text string. We're able to use that number to tell the MID function where to parse from. We can also use the FIND function to determine the number of characters to return by looking for the number of characters between two special characters, such as the leading slash and the next slash that bracket the 'Day' in your text string. Does this make sense?

Again, if you can use Paul's suggestion you would end up with a formula that didn't look quite as intimidating.

cheers,

Kelly

J. Angel

Hey Paul,

I have no control over how the columns are set up/arranged. This is a direct export (excel) from an incident tracker website.

J. Angel

Thank you, Kelly. The formula worked for me.