This is an easy one, though not for me apparently!

I have a variable cell, say cell B34, with 5/31/2007 currently in it,
formatted as a date. In another cell, I want it to say "as of end of May",
using the text function at the end to produce May. But I am having trouble
with the formatting of that text part, e.g., text(B34,"mmm") or something
like that, but that doesn't work - it gives me January. I tried the month
function, and it does yield 5, but I can't figure out how to get it to
produce May, the fifth month.

Kindly help.

Thanks!
Dean

Hi Dean,

Are you sure B34 is a date? Format it as General; it should read 39233. If it gives you some small number, that might be the
result of dividing 5 by 31 and then by 2007 and Excel's date system would indeed interpret that as January.
BTW, use "mmmm" (4 m's) as format code; no difference for May, but January would read Jan with 3 m's.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dean" wrote in message ...
| This is an easy one, though not for me apparently!
|
| I have a variable cell, say cell B34, with 5/31/2007 currently in it,
| formatted as a date. In another cell, I want it to say "as of end of May",
| using the text function at the end to produce May. But I am having trouble
| with the formatting of that text part, e.g., text(B34,"mmm") or something
| like that, but that doesn't work - it gives me January. I tried the month
| function, and it does yield 5, but I can't figure out how to get it to
| produce May, the fifth month.
|
| Kindly help.
|
| Thanks!
| Dean
|
|

It does show 39233 if formatted as general. Any other ideas?

"Niek Otten" wrote in message
...
Hi Dean,

Are you sure B34 is a date? Format it as General; it should read 39233. If
it gives you some small number, that might be the
result of dividing 5 by 31 and then by 2007 and Excel's date system would
indeed interpret that as January.
BTW, use "mmmm" (4 m's) as format code; no difference for May, but January
would read Jan with 3 m's.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dean" wrote in message
...
| This is an easy one, though not for me apparently!
|
| I have a variable cell, say cell B34, with 5/31/2007 currently in it,
| formatted as a date. In another cell, I want it to say "as of end of
May",
| using the text function at the end to produce May. But I am having
trouble
| with the formatting of that text part, e.g., text(B34,"mmm") or
something
| like that, but that doesn't work - it gives me January. I tried the
month
| function, and it does yield 5, but I can't figure out how to get it to
| produce May, the fifth month.
|
| Kindly help.
|
| Thanks!
| Dean
|
|

Maybe: Format(B34, "mmmm")

"Dean" wrote:

It does show 39233 if formatted as general. Any other ideas?

"Niek Otten" wrote in message
...
Hi Dean,

Are you sure B34 is a date? Format it as General; it should read 39233. If
it gives you some small number, that might be the
result of dividing 5 by 31 and then by 2007 and Excel's date system would
indeed interpret that as January.
BTW, use "mmmm" (4 m's) as format code; no difference for May, but January
would read Jan with 3 m's.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dean" wrote in message
...
| This is an easy one, though not for me apparently!
|
| I have a variable cell, say cell B34, with 5/31/2007 currently in it,
| formatted as a date. In another cell, I want it to say "as of end of
May",
| using the text function at the end to produce May. But I am having
trouble
| with the formatting of that text part, e.g., text(B34,"mmm") or
something
| like that, but that doesn't work - it gives me January. I tried the
month
| function, and it does yield 5, but I can't figure out how to get it to
| produce May, the fifth month.
|
| Kindly help.
|
| Thanks!
| Dean
|
|

Good point; are we talking VBA or worksheet formulas?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JLGWhiz" wrote in message ...
| Maybe: Format(B34, "mmmm")
|
| "Dean" wrote:
|
| It does show 39233 if formatted as general. Any other ideas?
|
| "Niek Otten" wrote in message
| ...
| Hi Dean,
|
| Are you sure B34 is a date? Format it as General; it should read 39233. If
| it gives you some small number, that might be the
| result of dividing 5 by 31 and then by 2007 and Excel's date system would
| indeed interpret that as January.
| BTW, use "mmmm" (4 m's) as format code; no difference for May, but January
| would read Jan with 3 m's.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Dean" wrote in message
| ...
| | This is an easy one, though not for me apparently!
| |
| | I have a variable cell, say cell B34, with 5/31/2007 currently in it,
| | formatted as a date. In another cell, I want it to say "as of end of
| May",
| | using the text function at the end to produce May. But I am having
| trouble
| | with the formatting of that text part, e.g., text(B34,"mmm") or
| something
| | like that, but that doesn't work - it gives me January. I tried the
| month
| | function, and it does yield 5, but I can't figure out how to get it to
| | produce May, the fifth month.
| |
| | Kindly help.
| |
| | Thanks!
| | Dean
| |
| |
|
|
|
|
|

I'm sorry for not noting that. We are talking ordinary EXCEL, not macros.
So, I think I'm still stuck.

"Niek Otten" wrote in message
...
Good point; are we talking VBA or worksheet formulas?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"JLGWhiz" wrote in message
...
| Maybe: Format(B34, "mmmm")
|
| "Dean" wrote:
|
| It does show 39233 if formatted as general. Any other ideas?
|
| "Niek Otten" wrote in message
| ...
| Hi Dean,
|
| Are you sure B34 is a date? Format it as General; it should read
39233. If
| it gives you some small number, that might be the
| result of dividing 5 by 31 and then by 2007 and Excel's date system
would
| indeed interpret that as January.
| BTW, use "mmmm" (4 m's) as format code; no difference for May, but
January
| would read Jan with 3 m's.
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| "Dean" wrote in message
| ...
| | This is an easy one, though not for me apparently!
| |
| | I have a variable cell, say cell B34, with 5/31/2007 currently in
it,
| | formatted as a date. In another cell, I want it to say "as of end
of
| May",
| | using the text function at the end to produce May. But I am
having
| trouble
| | with the formatting of that text part, e.g., text(B34,"mmm") or
| something
| | like that, but that doesn't work - it gives me January. I tried
the
| month
| | function, and it does yield 5, but I can't figure out how to get
it to
| | produce May, the fifth month.
| |
| | Kindly help.
| |
| | Thanks!
| | Dean
| |
| |
|
|
|
|
|

Or: Format(B34.Value, "mmmm") to be more precise.

"Dean" wrote:

It does show 39233 if formatted as general. Any other ideas?

"Niek Otten" wrote in message
...
Hi Dean,

Are you sure B34 is a date? Format it as General; it should read 39233. If
it gives you some small number, that might be the
result of dividing 5 by 31 and then by 2007 and Excel's date system would
indeed interpret that as January.
BTW, use "mmmm" (4 m's) as format code; no difference for May, but January
would read Jan with 3 m's.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Dean" wrote in message
...
| This is an easy one, though not for me apparently!
|
| I have a variable cell, say cell B34, with 5/31/2007 currently in it,
| formatted as a date. In another cell, I want it to say "as of end of
May",
| using the text function at the end to produce May. But I am having
trouble
| with the formatting of that text part, e.g., text(B34,"mmm") or
something
| like that, but that doesn't work - it gives me January. I tried the
month
| function, and it does yield 5, but I can't figure out how to get it to
| produce May, the fifth month.
|
| Kindly help.
|
| Thanks!
| Dean
|
|