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?
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,611
Office Version
  1. 365
Platform
  1. Windows
I tried this, with the system format settings set both ways, however these formulas both result in #value errors.
With a text string representing a date that has been entered in M/D/Y HH:MM AM/PM format the formula that I provided will work with any regional settings.
With a numeric date it would return an error but the MOD / INT formulas would work.
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

helpneeded2

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

View attachment 48233
Although changing the short date to M/d/yyyy does then allow DATEVALUE to work, I don't want to change my system settings to display that way, just so that I can use this spreadsheet.
 

helpneeded2

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
With a text string representing a date that has been entered in M/D/Y HH:MM AM/PM format the formula that I provided will work with any regional settings.
With a numeric date it would return an error but the MOD / INT formulas would work.
I wish you could see my computer screen then, as I get #value messages when try using those formulas. I made sure to open a fresh sheet, and copied and pasted the date from your example sheet into my sheet as 'values only' to ensure there were no hidden characters that could cause any issues.
 

helpneeded2

New Member
Joined
Jun 25, 2021
Messages
24
Office Version
  1. 2010
Platform
  1. Windows
With a text string representing a date that has been entered in M/D/Y HH:MM AM/PM format the formula that I provided will work with any regional settings.
With a numeric date it would return an error but the MOD / INT formulas would work.

Okay your example does indeed work. Thank you so much for this. That is some masterful work of code that I can't even begin to understand how it works.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,589
Office Version
  1. 2013
Platform
  1. Windows
Although changing the short date to M/d/yyyy does then allow DATEVALUE to work, I don't want to change my system settings to display that way, just so that I can use this spreadsheet.
I can genuinally imagine that, but it's still strange that both suggestions, Jason's and mine's, don't work for you.
I (also) don't have a mm/dd/yyyy setting, but I don't get a #Value error, although day and month ar switched, because of the interpretation I mentioned.
Nevertheless, glad you have a solution (y)
 
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,862
Messages
5,766,809
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