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!

 

[Joe4]

Welcome to the Board!

OK, so let's say that you have them separated via your Right/Left functions so that you currently have:
Sep 19 2017 in cell B1
and
10:08AM in cell C1

In order to use DateValue on the value in B1, we need to insert a comma after the day. This formula will do both that and the DateValue conversion in one step:
=DATEVALUE(LEFT(B1,LEN(B1)-5) & "," & RIGHT(B1,5))

In order to use TimeValue on the value in C1, we need to insert a space before the PM. This formula will do both that and the TimeValue conversion in one step:
=TIMEVALUE(LEFT(C1,LEN(C1)-2) & " " & RIGHT(C1,2))

If you get any errors due to any leading/trailing spaces, surround your RIGHT/LEFT formulas with the TRIM function to trim off the extra spaces.

 

[sgroath]

if you are only trying to show it in 2 cells as the date and the time respectively, make a 2nd cell reference the first (e.g., =A1) and then right click in each cell, select Format Cells, and select the Time and Date formats you want in the Number tab. If you are trying to do something else, please clarify what you want to do.

 

[MARK858]

@ sgroath, that only works with what Excel sees as True dates. Excel shouldn't recognise the string as a date with what the OP has posted in message 1

 

[Rick Rothstein]

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.

Do your date text strings have leading zeroes when the day number is less than 10 or the time value's hour is less than 10? In other words, which of these would your text have...

Sep 9 2017 8:08AM

Sep 09 2017 08:08AM

 

[ExcelUserZero]

Thank you for responding. The date and time strings do not have leading zeroes when less than ten.

 

[Norie]

Try this.

1 Select the column with the 'dates'.

2 Goto Data>Text to columns...

3 Choose Fixed Width on the 1st step.

4 On the 2nd step set one break line after the year.

5 On the 3rd step set the Column data format of the first field to MDY, and choose not to import the second field.

6 Click Finish.

 

[ExcelUserZero]

Thank you for your response.

I tried the formulas above and they work great! Is there a way to combine the formulas so I can assign the values without separating the date and time string?

 

[Joe4]

I tried the formulas above and they work great! Is there a way to combine the formulas so I can assign the values without separating the date and time string?

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

 

[Tetra201]

Try

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A1)," ",", ",2),"AM"," AM"),"PM"," PM")