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]

I have even tried just manually typing in "10/1/2021 5:00:00 PM" into A1 and then applying DATEVALUE(A1) to it, and still I get the error.

 

[jasonb75]

Did the alternative functions that I suggested work? (unlikely given that @GWteB's suggestion returned an error).

If they are all failing then it would mean that there is something else in your data that you have not told us about.

There is a possibility of certain dates not working correctly if the source data is in MDY format and your system settings are in DMT (or vice versa) but the times would still extract correctly.

 

[helpneeded2]

Did the alternative functions that I suggested work? (unlikely given that @GWteB's suggestion returned an error).

If they are all failing then it would mean that there is something else in your data that you have not told us about.

There is a possibility of certain dates not working correctly if the source data is in MDY format and your system settings are in DMT (or vice versa) but the times would still extract correctly.

No they didn't.

I read this article which also indicates the problem could be related to the system time (How to correct a #VALUE! error in the DATEVALUE function).

I'm really not sure what system time refers to but I am assuming it is the time settings on my computer. My time settings are:
Calendar: Gergorian Calendar
Short date: 2021-10-02
Long date: October-02-21
Short time: 12:43 PM
Long time: 12:43:41 PM

 

[jasonb75]

If it was an error caused by settings then the time part should still work.

Are you actually using Excel 2010 as per your profile, or an alternative application?

When you entered the data and time manually it is likely that the DATEVALUE and TIMEVALUE functions would have failed, but the MOD and INT functions should have worked.

 

[helpneeded2]

I have now changed my system time to MM/DD/YYYY format, and now DATEVALUE is working. While this is great that I found out what the problem is, I really don't want to change my system display settings every time I want to use this spreadsheet. I will just have to use multiple columns to break the date and hours apart. I was really hoping there was a single cell formula way of doing this -- which technically there is with DATEVALUE, but not when it is system date format dependant.

 

[helpneeded2]

If it was an error caused by settings then the time part should still work.

Are you actually using Excel 2010 as per your profile, or an alternative application?

When you entered the data and time manually it is likely that the DATEVALUE and TIMEVALUE functions would have failed, but the MOD and INT functions should have worked.

Excel 365

 

[helpneeded2]

If it was an error caused by settings then the time part should still work.

Are you actually using Excel 2010 as per your profile, or an alternative application?

When you entered the data and time manually it is likely that the DATEVALUE and TIMEVALUE functions would have failed, but the MOD and INT functions should have worked.

I tested this. The MOD and INT only work, when I change the system format to mm/dd/yyyy.

 

[jasonb75]

When I looked at your settings, I thought that it should work based on long date but that would appear to be incorrect. See if this works with your normal settings

Book1
	A	B	C
2	10/1/2021 5:00:00 PM	01 October 2021	17:00:00
Sheet1

Cell Formulas
Range	Formula
B2	B2	=--LET(f,FIND(" ",A2)-1,d,LEFT(A2,f),s,d&"/"&d,MID(s,FIND("|",SUBSTITUTE(s,"/","|",2))+1,f))
C2	C2	=--MID(A2,FIND(" ",A2)+1,255)

 

[helpneeded2]

When I looked at your settings, I thought that it should work based on long date but that would appear to be incorrect. See if this works with your normal settings

Book1
	A	B	C
2	10/1/2021 5:00:00 PM	01 October 2021	17:00:00
Sheet1

Cell Formulas
Range	Formula
B2	B2	=--LET(f,FIND(" ",A2)-1,d,LEFT(A2,f),s,d&"/"&d,MID(s,FIND("|",SUBSTITUTE(s,"/","|",2))+1,f))
C2	C2	=--MID(A2,FIND(" ",A2)+1,255)

I tried this, with the system format settings set both ways, however these formulas both result in #value errors.

 

[GWteB]

I'm really not sure what system time refers to but I am assuming it is the time settings on my computer. My time settings are:
Calendar: Gergorian Calendar
Short date: 2021-10-02
Long date: October-02-21
Short time: 12:43 PM
Long time: 12:43:41 PM

These are just display settings. Your locale determines how to interpret textual representations of date and time. The Windows OS makes use of this and usually most applications too, including Excel.
To view your Windows locale open Control Panel (type in search bar) > click on Clock, Language and Region > click on Change date, time or number formats.
Normally these settings are always correct and you never need to change them.