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

helpneeded2

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
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
ABC
210/1/2021 5:00:00 PM01 October 202117:00:00
Sheet1
Cell Formulas
RangeFormula
B2B2=--LET(f,FIND(" ",A2)-1,d,LEFT(A2,f),s,d&"/"&d,MID(s,FIND("|",SUBSTITUTE(s,"/","|",2))+1,f))
C2C2=--MID(A2,FIND(" ",A2)+1,255)
 
Solution

helpneeded2

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
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
ABC
210/1/2021 5:00:00 PM01 October 202117:00:00
Sheet1
Cell Formulas
RangeFormula
B2B2=--LET(f,FIND(" ",A2)-1,d,LEFT(A2,f),s,d&"/"&d,MID(s,FIND("|",SUBSTITUTE(s,"/","|",2))+1,f))
C2C2=--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

Well-known Member
Joined
Sep 10, 2010
Messages
2,589
Office Version
  1. 2013
Platform
  1. Windows
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.

ScreenShot238.jpg
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,863
Messages
5,766,821
Members
425,379
Latest member
thedoctor00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top