Converting Text dates to date values

[ExcelUserZero]

Good morning,

I have a set of data that I imported into Excel. The date and time comes in one cell, but in the format: Sep 19 2017 10:08AM. I used the =Right and =Left functions to separate the date from the time, but now I am stuck with how to convert these to actual values. I have tried messing around with =datevalue and =timevalue.

Thank you in advance for your help!

 

[ExcelUserZero]

Do you want your result to be one valid date/time value, and do you need/want one for date and one for time?

Hey @Joe4, yes - I would like one date/time value - 9/19/2017 10:08

My end goal is to use these times to extract elapsed time between two events, but I already have formula that performs that task - as long as the times and dates are in the format above.

Again, thank you so much for your help - I have really learned a lot today!

 

[Joe4]

Did you try Tetra201's formula back in post 10?
It looks like that should do what you want.

 

[ExcelUserZero]

Thank you! I will try that one in the morning - have not used the substitute function often, so was a bit nervous about using it.

Have a great night!