Convert string WED FEB 23, 2009 to yyyy/mm/dd

[kpaull]

I have a file with the following string in the date field:

"8:26 am TUE FEB 24, 2009", how can I change this field to a string with yyyy/mm/dd HH:MM (24hr format)?

Thanks,
Kurtis

 

[Andrew Poulsom]

Try eg:

=SUBSTITUTE(SUBSTITUTE(RIGHT(A1,12),LEFT(RIGHT(A1,12),4),""),","," " &LEFT(RIGHT(A1,12),3))+LEFT(A1,FIND(" ",A1,7)-1)

formatted as yyyy/mm/dd hh:mm.

 

[jim may]

Here are the components - Can you combine into a one cell formula?

Cell A1
8:26 am TUE FEB 24, 2009 B1=DATEVALUE(RIGHT(A1,12))

B2=TIMEVALUE(LEFT(A1,7))

B3=TEXT(B1,"yyyy/mm/dd") & " " & TEXT(B2,"HH:MM")

 

[Andrew Poulsom]

Jim,

The formula:

=DATEVALUE(RIGHT(A1,12))

doesn't work if the regional settings are eg English(United Kingdom). That's why I had to turn it into 24 FEB 2009.

 

[jim may]

Thanks Andrew,

Being somewhat limited to my Virginia USA clients, I'm not International -- and
thus unfamiliar with such issues. LOL

Jim

 

[Ron Coderre]

With
A1: containing time/date text like your sample
example: 8:26 am TUE FEB 24, 2009

Try this (formatted as yyyy/mm/dd hh:mm):

B1: =DATE(RIGHT(A1,4),MONTH("1 "&LEFT(RIGHT(A1,12),4)),LEFT(RIGHT(A1,8),2))+
LEFT(A1,8)

In the above example, the formula returns: 2009/02/24 08:26

Is that something you can work with?

 

[jim may]

WoooooW, Ron
That is Nice!!!!
Thanks for contributing here

 

[kpaull]

With
A1: containing time/date text like your sample
example: 8:26 am TUE FEB 24, 2009

Try this (formatted as yyyy/mm/dd hh:mm):

B1: =DATE(RIGHT(A1,4),MONTH("1 "&LEFT(RIGHT(A1,12),4)),LEFT(RIGHT(A1,8),2))+
LEFT(A1,8)

In the above example, the formula returns: 2009/02/24 08:26

Is that something you can work with?

Rod this worked, you nailed it. Thanks!

Andrew and Jim thank you. Here is some feedback on your proposed formulas:

Try eg:

=SUBSTITUTE(SUBSTITUTE(RIGHT(A1,12),LEFT(RIGHT(A1,12),4),""),","," " &LEFT(RIGHT(A1,12),3))+LEFT(A1,FIND(" ",A1,7)-1)

formatted as yyyy/mm/dd hh:mm.

Andrew, using your above formula works, somewhat. I neglected to inform you that the date string changes with single digit dates: 9:43 am FRI MAR 2, 2009

And for your formula returns #VALUE! on the single digit dates.

Jim,

The formula:

=DATEVALUE(RIGHT(A1,12))

doesn't work if the regional settings are eg English(United Kingdom). That's why I had to turn it into 24 FEB 2009.

Kinda works. First of all my regional settings are English(USA). For 9:43 am FRI MAR 2, 2009
it returns 2009/03/02 00:00 when formatted as yyyy/mm/dd hh:mm. and returns the same with 9:43 am FRI MAR 20, 2009.

Thanks All, problem solved.

 

[barry houdini]

One more option. Works with US date settings only,

=TRIM(MID(A1&" "&A1,13,LEN(A1)-3))+0

format as yyyy/mm/dd hh:mm

 

[kpaull]

Barry,

It kinda works. It fails on string "10:18 am THU MAR 19, 2009" but works on string "9:19 am THU MAR 19, 2009"

Kurtis