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

 

[barry houdini]

Hello Kurtis,

I tested with both types of times and it worked for me, the TRIM function should sort that out, but perhaps instead of spaces you have CHAR(160) in the text string, does this version work?

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

 

[kpaull]

Barry, still shows #VALUE! for <TABLE style="WIDTH: 134pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=179 border=0 x:str><COLGROUP><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD id=td_post_1879954 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 134pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=179 height=17 x:str="'10:09 am FRI MAR 13, 2009">10:09 am FRI MAR 13, 2009</TD></TR></TBODY></TABLE>and works fine for <TABLE style="WIDTH: 134pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=179 border=0 x:str><COLGROUP><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD id=td_post_1879954 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 134pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=179 height=17 x:str="'9:20 am THU MAR 12, 2009">9:20 am THU MAR 12, 2009</TD></TR></TBODY></TABLE>

Not sure about spacings vs. any special characters. I suspect it doesn't have any special characters.

Weird, eh?

Kurtis

 

[Ron Coderre]

Is the issue a LEADING space?

Try this:

=--TRIM(MID(TRIM(A1&" "&A1),13,LEN(TRIM(A1))-4))code]
Does that work?

 

[kpaull]

Ron,

That worked like a charm.

Cheers,
Thanks you guys, now I have two solutions.!

 

[barry houdini]

..but wouldn't a leading space also cause Ron's original suggestion to fail? Perhaps that needs some TRIMming too

 

[Ron Coderre]

..but wouldn't a leading space also cause Ron's original suggestion to fail? Perhaps that needs some TRIMming too

Yup...But, with the new circumstances, I'd use this formula to maintain international compatibility:

=--(SUBSTITUTE(TRIM(RIGHT(TRIM(A1),8)),", "," "&MID(TRIM(A1),
FIND(",",TRIM(A1))-6,4))&" "&LEFT(A1,8))

That formula properly translates each of these strings:

" 9:19 am THU MAR 19, 2009 "
" 9:19 am THU MAR 19, 2009"
"9:19 am THU MAR 19, 2009 "
"9:19 am THU MAR 19, 2009"
" 10:18 pm THU MAR 19, 2009 "
" 10:18 pm THU MAR 19, 2009"
"10:18 pm THU MAR 19, 2009 "
"10:18 pm THU MAR 19, 2009"
" 9:05 am THU MAR 5, 2009 "
" 9:05 am THU MAR 5, 2009"
"9:05 am THU MAR 5, 2009 "
"9:05 am THU MAR 5, 2009"
" 10:18 pm THU MAR 5, 2009 "
" 10:18 pm THU MAR 5, 2009"
"10:18 pm THU MAR 5, 2009 "
"10:18 pm THU MAR 5, 2009"