Challenge Extracting Various TIME Values from Cells

[chaunb]

I've gotten pretty good with extracting various types of content from cells. But this is a head scratcher. I want convert time values from imported text data, but the time formats are different. I started building a long formula using a series of IF, COUNTIF, MID, FIND, AND, OR, and TIME functions, but I'm having trouble building ONE formula or array that can go through the various time and text formats to return the desired results in the START DATE and END DATE columns.

NOTE: The second to last line "8:00:00 PM" returns a text value of "0.833333333"...not sure why.

And if possible, I would like to highlight cells that need to be reviewed for further clarification (see the 3 lines below data examples).

FYI: There is also a date column I will be merging with the result calculations.

Times from Import​	Start Date	End Date
10AM-Noon	10:00:00	12:00:00
9pm-12am	21:00:00	0:00:00
Tue-Fri 10-4; Sat 10-2	10:00:00, 10:00:00	16:00:00, 14:00:00
5:00-7:30 pm	17:00:00	19:30:00
6:30pm to 9:00pm	18:30:00	21:00:00
5-7:30pm	17:00:00	19:30:00
12-5pm. Guided tours available every half-hour; last tour begins at 4:30pm	12:00:00	17:00:00
Monday-Saturday 10-6 Sunday 12-5	10:00:00, 12:00:00	12:00:00, 17:00:00
9 a.m. to 5 p.m. Daily	9:00:00	17:00:00
By appointment starting at 10:00am	10:00:00
10:00-11:30AM	10:00:00	11:30:00
2:00-4:00PM	14:00:00	16:00:00
8:00:00 PM​	20:00:00
​
​
OR if a number isn't followed by a AM or PM,	​
OR if no times are found.	​

<tbody>
[TD="colspan: 2"] Time varies, please visit: http://tickets... [/TD]

[TD="colspan: 3"] Cell automatically highlights if a DAY of the week is detected (i.e. Fri or Friday), [/TD]

</tbody>

PLEASE HELP!!!!!!!