Best way to convert a text string containing date and time into Excel date and times?

[helpneeded2]

The data I am drawing from formats the date and time in a single text value:

10/1/2021 5:00:00 PM

I wish to have a column on my sheet to show the date, and a separate one to show the time. The tricky part, is that as show in the example below, the month may consist of 1 or 2 characters, and the date also similarly will show the hours in 1 or 2 characters. While I am working on a very long formula to try to accomplish this I'm thinking there has to be a much easier way. What would be the easiest way to split the time and date apart and then convert them into a date() and time() value?

 

[helpneeded2]

Here is the formula I am currently using to get the date:
=IF('RAW DATA'!A2="","",DATE(RIGHT(TRIM(LEFT('RAW DATA'!A2,10)),4),IF(MID('RAW DATA'!A2,5,1)="/",MID('RAW DATA'!A2,4,1),MID('RAW DATA'!A2,4,2)),IF(MID('RAW DATA'!A2,2,1)="/",LEFT('RAW DATA'!A2,1),LEFT('RAW DATA'!A2,2))))

 

[helpneeded2]

I really wish I could edit my prior posts. Here is the working formula for above (I had the month and day switched):

=IF('RAW DATA'!A2="","",DATE(RIGHT(TRIM(LEFT('RAW DATA'!A2,10)),4),IF(MID('RAW DATA'!A2,2,1)="/",LEFT('RAW DATA'!A2,1),LEFT('RAW DATA'!A2,2)),IF(MID('RAW DATA'!A2,5,1)="/",MID('RAW DATA'!A2,4,1),MID('RAW DATA'!A2,4,2))))

Surely there is a better way of doing this?

 

[helpneeded2]

Actually I now realize that won't work as I didn't take into account that both the day and the month might be 1 or 2 characters.

 

[jasonb75]

Unless there are hidden characters in the date string that prevent a formula from reading it properly,
Date

=INT(A2)

Time

=MOD(A2,1)

You will need to format the formula cells as required.

 

[GWteB]

The below would also work

=DATEVALUE(A1)

and

=TIMEVALUE(A1)

 

[helpneeded2]

The below would also work

=DATEVALUE(A1)

and

=TIMEVALUE(A1)

DATEVALUE and TIMEVALUE both produce a #Value error.

 

[jasonb75]

DATEVALUE and TIMEVALUE both produce a #Value error.

Sounds like there are hidden characters in the string. Is the data source copy and paste from a web page?

 

[GWteB]

Then you have more characters in that text string

Book1
	A	B	C
1	10/1/2021 5:00:00 PM	10-1-2021	17:00:00
Sheet1

Cell Formulas
Range	Formula
B1	B1	=DATEVALUE(A1)
C1	C1	=TIMEVALUE(A1)

 

[helpneeded2]

Sounds like there are hidden characters in the string. Is the data source copy and paste from a web page?

No, they are copied from an excel sheet converted from a csv file.