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?

 

[jasonb75]

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.

 

[helpneeded2]

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]

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]

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]

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