Retrieve Date from Data

Amber Jackson

Good morning gurus!

I have a tricky one today. I need help retrieving data from a cell. The column (Routine Execution) contains information imported from another program and looks like this:

5S Audit - Jul 29, 2022, 04:01 PM

Gemba Checklist - July 13, 2022

I was able to pull the date from the above data using the following formula:

=IF(CONTAINS("5S", [Routine Execution]@row), RIGHT([Routine Execution]@row, (LEN([Routine Execution]@row) - 11)), RIGHT([Routine Execution]@row, (LEN([Routine Execution]@row) - 19)))

That gives me the "date" portion of the cell. I need to break down the month and year using helper columns. But using MONTH gives me an INVALID DATA TYPE error. So does pretty much any other formula I have tried. This error exists if the column is a Text/Number or a Date format.

Does anyone have any ideas? @Paul Newcome and @Andrée Starå your advice has never failed me--here's hoping you can save me again! 😅

Paul Newcome

Ok. This should take care of things then:

=DATE(VALUE(MID([Routine Execution]@row, FIND(",", [Routine Execution]@row) + 2, 4)), IF(CONTAINS("Jan", [Routine Execution]@row), 1, IF(CONTAINS("Feb", [Routine Execution]@row), 2, IF(CONTAINS("Mar", [Routine Execution]@row), 3, IF(CONTAINS("Apr", [Routine Execution]@row), 4, IF(CONTAINS("May", [Routine Execution]@row), 5, IF(CONTAINS("Jun", [Routine Execution]@row), 6, IF(CONTAINS("Jul", [Routine Execution]@row), 7, IF(CONTAINS("Aug", [Routine Execution]@row), 8, IF(CONTAINS("Sep", [Routine Execution]@row), 9, IF(CONTAINS("Oct", [Routine Execution]@row), 10, IF(CONTAINS("Nov", [Routine Execution]@row), 11, 12))))))))))), VALUE(MID([Routine Execution]@row, FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 5)) + 1, FIND(",", [Routine Execution]@row) - (FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 5)) + 1))))

Paul McGuinness

Hi @Amber Jackson

Tried this a different way to allow it to work regardless of month, Formula below appears to work on any dates, I wasn't sure how dates before the tenth of the month were represented but the formula picks them up anyway. Tested as below:

=IF(CONTAINS("5S", [Routine execution]@row), LEFT(RIGHT([Routine execution]@row, 18), 2), LEFT(RIGHT([Routine execution]@row, 8), 2)) + "/" + IF(CONTAINS("Jan", [Routine execution]@row), 1, IF(CONTAINS("Feb", [Routine execution]@row), 2, IF(CONTAINS("Mar", [Routine execution]@row), 3, IF(CONTAINS("Apr", [Routine execution]@row), 4, IF(CONTAINS("May", [Routine execution]@row), 5, IF(CONTAINS("Jun", [Routine execution]@row), 6, IF(CONTAINS("Jul", [Routine execution]@row), 7, IF(CONTAINS("Aug", [Routine execution]@row), 8, IF(CONTAINS("Sep", [Routine execution]@row), 9, IF(CONTAINS("Oct", [Routine execution]@row), 10, IF(CONTAINS("Nov", [Routine execution]@row), 11, IF(CONTAINS("Dec", [Routine execution]@row), 12, "Error")))))))))))) + "/" + IF(CONTAINS("5S", [Routine execution]@row), LEFT(RIGHT([Routine execution]@row, 14), 4), RIGHT([Routine execution]@row, 4))

Hope this helps

Thanks

Paul

Amber Jackson

@Paul McGuinness This almost works...the day and month are backwards. (Ex: July 29th displays as 29/07/22 instead of 07/29/22). Any ideas?

Paul Newcome

This should output an actual date value without the need for helper columns:

=DATE(VALUE(MID([Routine Execution]@row, FIND(",", [Routine Execution]@row) + 2, 4)), IF(CONTAINS("Jan", [Routine Execution]@row), 1, IF(CONTAINS("Feb", [Routine Execution]@row), 2, IF(CONTAINS("Mar", [Routine Execution]@row), 3, IF(CONTAINS("Apr", [Routine Execution]@row), 4, IF(CONTAINS("May", [Routine Execution]@row), 5, IF(CONTAINS("Jun", [Routine Execution]@row), 6, IF(CONTAINS("Jul", [Routine Execution]@row), 7, IF(CONTAINS("Aug", [Routine Execution]@row), 8, IF(CONTAINS("Sep", [Routine Execution]@row), 9, IF(CONTAINS("Oct", [Routine Execution]@row), 10, IF(CONTAINS("Nov", [Routine Execution]@row), 11, 12))))))))))), VALUE(MID([Routine Execution]@row, FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 4)) + 1, FIND(",", [Routine Execution]@row) - (FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 4)) + 1))))

Amber Jackson

@Paul Newcome we are close...so close...

So it looks like we forgot the Dec portion of the "month" section of the formula. I added that part in. The month and year seem to work, but the day section of the formula is throwing an INCORRECT ARGUMENT error.

=DATE(VALUE(MID([Routine Execution]@row, FIND(",", [Routine Execution]@row) + 2, 4)), IF(CONTAINS("Jan", [Routine Execution]@row), 1, IF(CONTAINS("Feb", [Routine Execution]@row), 2, IF(CONTAINS("Mar", [Routine Execution]@row), 3, IF(CONTAINS("Apr", [Routine Execution]@row), 4, IF(CONTAINS("May", [Routine Execution]@row), 5, IF(CONTAINS("Jun", [Routine Execution]@row), 6, IF(CONTAINS("Jul", [Routine Execution]@row), 7, IF(CONTAINS("Aug", [Routine Execution]@row), 8, IF(CONTAINS("Sep", [Routine Execution]@row), 9, IF(CONTAINS("Oct", [Routine Execution]@row), 10, IF(CONTAINS("Nov", [Routine Execution]@row), 11, IF(CONTAINS("Dec", [Routine Execution]@row), 12))))))))))), VALUE(MID([Routine Execution]@row, FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 4)) + 1, FIND(",", [Routine Execution]@row) - (FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 4)) + 1))))

What are the asterisks for? I'm not familiar with that function within a formula for Smartsheet.

Paul McGuinness

HI @Amber Jackson

Just needed to swap the date and month portions around in the formula, British date formatting :)

This should display as you wanted now.

=IF(CONTAINS("Jan", [Routine execution]@row), 1, IF(CONTAINS("Feb", [Routine execution]@row), 2, IF(CONTAINS("Mar", [Routine execution]@row), 3, IF(CONTAINS("Apr", [Routine execution]@row), 4, IF(CONTAINS("May", [Routine execution]@row), 5, IF(CONTAINS("Jun", [Routine execution]@row), 6, IF(CONTAINS("Jul", [Routine execution]@row), 7, IF(CONTAINS("Aug", [Routine execution]@row), 8, IF(CONTAINS("Sep", [Routine execution]@row), 9, IF(CONTAINS("Oct", [Routine execution]@row), 10, IF(CONTAINS("Nov", [Routine execution]@row), 11, IF(CONTAINS("Dec", [Routine execution]@row), 12, "Error")))))))))))) + "/" +IF(CONTAINS("5S", [Routine execution]@row), LEFT(RIGHT([Routine execution]@row, 18), 2), LEFT(RIGHT([Routine execution]@row, 8), 2)) + "/" + IF(CONTAINS("5S", [Routine execution]@row), LEFT(RIGHT([Routine execution]@row, 14), 4), RIGHT([Routine execution]@row, 4))

Thanks

Paul

Paul Newcome

I left the "Dec" out because that was the only one left. Basically I said that if it is not one of the other 11 months then output 12 for Dec.

The reason the day portion is no longer working is because you did not include another closing parenthesis in the month portion when adding the extra IF statement.

Here is a breakdown of the day portion:

VALUE(MID([Routine Execution]@row, FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 4)) + 1, FIND(",", [Routine Execution]@row) - (FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 4)) + 1)))

FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 4)) + 1

This replaces the fourth space with an asterisk. We then use the FIND function to locate the asterisk. FIND + 1 is our starting position in the MID function.

FIND(",", [Routine Execution]@row)

This finds our comma (first one comes after the day).

Then we subtract from that our *+1 to get how many characters for the MID function. Wrapping that in the VALUE function will convert that into a number which is used for the day portion of the DATE function.

Here it is working in my test sheet including leaving out the "Dec" IF.

Amber Jackson

Hi @Paul Newcome

Thanks for breaking this down for me. I copied everything exactly like you have it and I am still getting an error (#INVALID VALUE). I'm not sure what the difference is...

Paul Newcome

Are you able to provide a screenshot of that formula AND the [Routine Execution] column? AS you can see in my screenshot it is working as expected in my test sheet when I copy/paste "5S Audit - Jul 29, 2022, 04:01 PM" and "Gemba Checklist - July 13, 2022" from your original post. I want to see if there are maybe any differences because the formula provided is definitely specific to those two text strings (or at least some commonalities between the two).

The formula assumes that the first comma in the string will be before the year and that there will be a space between the comma and year. It is also assuming that the day comes after the 4th space and has a comma immediately after it.

Amber Jackson

@Paul Newcome here you go. Hope this helps!

Paul Newcome

So there is the problem. The formula was not written for the "SCAPI" at the beginning of the text string.

Will the "5S" entries also have that?

Amber Jackson

My apologies @Paul Newcome --I should have included the entire name from the beginning! Here are the names of the two possibilities:

Paul Newcome

Ok. This should take care of things then:

=DATE(VALUE(MID([Routine Execution]@row, FIND(",", [Routine Execution]@row) + 2, 4)), IF(CONTAINS("Jan", [Routine Execution]@row), 1, IF(CONTAINS("Feb", [Routine Execution]@row), 2, IF(CONTAINS("Mar", [Routine Execution]@row), 3, IF(CONTAINS("Apr", [Routine Execution]@row), 4, IF(CONTAINS("May", [Routine Execution]@row), 5, IF(CONTAINS("Jun", [Routine Execution]@row), 6, IF(CONTAINS("Jul", [Routine Execution]@row), 7, IF(CONTAINS("Aug", [Routine Execution]@row), 8, IF(CONTAINS("Sep", [Routine Execution]@row), 9, IF(CONTAINS("Oct", [Routine Execution]@row), 10, IF(CONTAINS("Nov", [Routine Execution]@row), 11, 12))))))))))), VALUE(MID([Routine Execution]@row, FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 5)) + 1, FIND(",", [Routine Execution]@row) - (FIND("*", SUBSTITUTE([Routine Execution]@row, " ", "*", 5)) + 1))))

Amber Jackson

@Paul Newcome you are an absolute genius. I wouldn't have be able to do it without you! Thank you so much!!