Text Date to Date

[dantheram]

Hi all,

I have cells with the following date format "3 Aug, 2017 10:00AM"

Is there a simple way to split out the date?

Dan

 

[Jonmo1]

If that's a consistent format, try

=SUBSTITUTE(LEFT(A1,12),",","")+0

 

[dantheram]

This works fine, thanks.

Is there a way to rearrange for "Aug 3, 2017 10:00AM"

 

[Jonmo1]

You could just remove the substitute

=LEFT(A1,12)+0

 

[dantheram]

You could just remove the substitute

=LEFT(A1,12)+0

Any way to solve for the american style?

 

[Jonmo1]

Any way to solve for the american style?

You'll have to be more specific. Americans have many styles.

And please stop the bait and switch.
Solve A,
Solved
Now how about B
Solved
Also C
This is called bait and switch.

Please post ALL possible formats of your date up front, rather than one at a time.

 

[dantheram]

You'll have to be more specific. Americans have many styles.

And please stop the bait and switch.
Solve A,
Solved
Now how about B
Solved
Also C
This is called bait and switch.

Please post ALL possible formats of your date up front, rather than one at a time.

I accept your point but in my defence i didn't notice i had any US dates until i'd reached row 1000.

Apologies if this has upset you

PS the style is "Aug 3, 2017"

Dan

 

[Jonmo1]

No worries, just an FYI.

PS the style is "Aug 3, 2017"

Isn't that what you posted in Post #3 ?
And I answered in Post #4 ?

 

[dantheram]

No worries, just an FYI.

Isn't that what you posted in Post #3 ?
And I answered in Post #4 ?

Your formula produces #value ! for "Aug 3, 2017 10:00AM"

 

[Jonmo1]

I see, you're using UK dates.

This should handle either Aug 3, or 3 Aug

=IFERROR(LEFT(A1,12)+0,DATE(MID(A1,8,5),MONTH(LEFT(A1,3)&1),MID(A1,5,LEN(A1)-18)))