Dear kind and wise people,

Can someone please help me. I cannot figure out what's wrong with my
formatting. I have searched this terrific board for an answer, but can't
find exactly the solution, and now time has run out, so I'm asking directly.

Have a column A1 with dates spelled out as follows:
Wed Mar 04 2009 15:01:59 EST.

I am trying to convert this into B1 in the format :
04-Mar-2009.

ATTEMPT #1 --
Here is what I have tried in Column B:
=MID(A1,5,11)
And I have chosen the Date format for Column B to be 04-Mar-2009
But no matter how I format Column B, it displays as
Mar 04 2009

ATTEMPT #2 --
I have typed in Column B
=DATEVALUE(MID(F27,5,11))
The cell now displays the error
#VALUE!

The columns are NOT protected. I have Excel 2007, working in a .xls
document in compatibility mode.

Can someone please tell me what's wrong?

Many, many thanks,
-Lynne

Try:

=(MID(A1,5,6)&", "&MID(A1,12,4))+0


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111086

Beautiful! Thank you so much!


"NBVC" wrote:


Try:

=(MID(A1,5,6)&", "&MID(A1,12,4))+0


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111086

QUESTION ON THIS, IF YOU PLEASE --
(don't know if anyone will see this, but here goes . . .)

I always try to learn from the answers, but this has me stumped. Why +0 is
needed at the end?

Thank you once again.
-Lynne


"NBVC" wrote:


Try:

=(MID(A1,5,6)&", "&MID(A1,12,4))+0


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111086

It coerces a text string, which is gotten from the MID() function, to a
number.. so that you can format as a date....(since Excel sees dates as
numbers.. i.e. serial numbers).


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111086

Thanks again. Really helpful!

"NBVC" wrote:


It coerces a text string, which is gotten from the MID() function, to a
number.. so that you can format as a date....(since Excel sees dates as
numbers.. i.e. serial numbers).


--
NBVC

Where there is a will there are many ways.
'The Code Cage' (http;//www.thecodecage.com)
------------------------------------------------------------------------
NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=111086