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" <(E-Mail Removed)> wrote in message news:wPKdnbBc-(E-Mail Removed)...
| 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:wPKdnbBc-(E-Mail Removed)...
> | 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:wPKdnbBc-(E-Mail Removed)...
> > | 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:wPKdnbBc-(E-Mail Removed)...
> > | 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" <(E-Mail Removed)> wrote in message news:38BE7752-7415-4CCF-A3D2-(E-Mail Removed)...
| Maybe: Format(B34, "mmmm")
|
| "Dean" wrote:
|
| > It does show 39233 if formatted as general. Any other ideas?
| >
| > "Niek Otten" <(E-Mail Removed)> wrote in message
| > news:(E-Mail Removed)...
| > > 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" <(E-Mail Removed)> wrote in message
| > > news:wPKdnbBc-(E-Mail Removed)...
| > > | 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Good point; are we talking VBA or worksheet formulas?
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news:38BE7752-7415-4CCF-A3D2-(E-Mail Removed)...
> | Maybe: Format(B34, "mmmm")
> |
> | "Dean" wrote:
> |
> | > It does show 39233 if formatted as general. Any other ideas?
> | >
> | > "Niek Otten" <(E-Mail Removed)> wrote in message
> | > news:(E-Mail Removed)...
> | > > 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" <(E-Mail Removed)> wrote in message
> | > > news:wPKdnbBc-(E-Mail Removed)...
> | > > | 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
> | > > |
> | > > |
> | > >
> | > >
> | >
> | >
> | >
>
>

<text(B34,"mmm") or something like that>

The last four words make me feel.... well....<g> For example, you can't get "January" with format code mmm. You can get "Jan".

Please check your formula and copy and paste (not re-type) it to any reply. Are you sure you point to the correct cell? Is
calculation set to Automatic (check, you might be surprised!)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Dean" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
| I'm sorry for not noting that. We are talking ordinary EXCEL, not macros.
| So, I think I'm still stuck.
|
| "Niek Otten" <(E-Mail Removed)> wrote in message
| news:(E-Mail Removed)...
| > Good point; are we talking VBA or worksheet formulas?
| >
| > --
| > Kind regards,
| >
| > Niek Otten
| > Microsoft MVP - Excel
| >
| > "JLGWhiz" <(E-Mail Removed)> wrote in message
| > news:38BE7752-7415-4CCF-A3D2-(E-Mail Removed)...
| > | Maybe: Format(B34, "mmmm")
| > |
| > | "Dean" wrote:
| > |
| > | > It does show 39233 if formatted as general. Any other ideas?
| > | >
| > | > "Niek Otten" <(E-Mail Removed)> wrote in message
| > | > news:(E-Mail Removed)...
| > | > > 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" <(E-Mail Removed)> wrote in message
| > | > > news:wPKdnbBc-(E-Mail Removed)...
| > | > > | 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
| > | > > |
| > | > > |
| > | > >
| > | > >
| > | >
| > | >
| > | >
| >
| >
|
|

Month (b4) shows up as 5.

=TEXT(MONTH(B4),"mmm")

is the formula I used, and cell B4 is:

5/1/2007, which shows up as a large number 32903 if I format it as general.
It is definitely pointing to the right cell. I had recalc except tables but
I changed it to automatic and the problem is still there. I closed out of
EXCEL but that did not help. Perhaps I should reboot?


Dean


"Niek Otten" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> <text(B34,"mmm") or something like that>
>
> The last four words make me feel.... well....<g> For example, you can't
> get "January" with format code mmm. You can get "Jan".
>
> Please check your formula and copy and paste (not re-type) it to any
> reply. Are you sure you point to the correct cell? Is
> calculation set to Automatic (check, you might be surprised!)
>
> --
> Kind regards,
>
> Niek Otten
> Microsoft MVP - Excel
>
>
> "Dean" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> | I'm sorry for not noting that. We are talking ordinary EXCEL, not
> macros.
> | So, I think I'm still stuck.
> |
> | "Niek Otten" <(E-Mail Removed)> wrote in message
> | news:(E-Mail Removed)...
> | > Good point; are we talking VBA or worksheet formulas?
> | >
> | > --
> | > Kind regards,
> | >
> | > Niek Otten
> | > Microsoft MVP - Excel
> | >
> | > "JLGWhiz" <(E-Mail Removed)> wrote in message
> | > news:38BE7752-7415-4CCF-A3D2-(E-Mail Removed)...
> | > | Maybe: Format(B34, "mmmm")
> | > |
> | > | "Dean" wrote:
> | > |
> | > | > It does show 39233 if formatted as general. Any other ideas?
> | > | >
> | > | > "Niek Otten" <(E-Mail Removed)> wrote in message
> | > | > news:(E-Mail Removed)...
> | > | > > 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" <(E-Mail Removed)> wrote in message
> | > | > > news:wPKdnbBc-(E-Mail Removed)...
> | > | > > | 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
> | > | > > |
> | > | > > |
> | > | > >
> | > | > >
> | > | >
> | > | >
> | > | >
> | >
> | >
> |
> |
>
>

=month(b4) will return a number between 1 and 12.

=text(1,"mmm") will return Jan since 1 is the same as January 1, 1900
=text(2,"mmm") will return Jan since 2 is the same as January 2, 1900
....
=text(12,"mmm") will return Jan since 12 is the same as January 12, 1900

If B4 contained a date, then:
=text(B4,"mmm") will return a 3 letter abbreviation of whatever month B4 holds.



Dean wrote:
>
> Month (b4) shows up as 5.
>
> =TEXT(MONTH(B4),"mmm")
>
> is the formula I used, and cell B4 is:
>
> 5/1/2007, which shows up as a large number 32903 if I format it as general.
> It is definitely pointing to the right cell. I had recalc except tables but
> I changed it to automatic and the problem is still there. I closed out of
> EXCEL but that did not help. Perhaps I should reboot?
>
> Dean
>
> "Niek Otten" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > <text(B34,"mmm") or something like that>
> >
> > The last four words make me feel.... well....<g> For example, you can't
> > get "January" with format code mmm. You can get "Jan".
> >
> > Please check your formula and copy and paste (not re-type) it to any
> > reply. Are you sure you point to the correct cell? Is
> > calculation set to Automatic (check, you might be surprised!)
> >
> > --
> > Kind regards,
> >
> > Niek Otten
> > Microsoft MVP - Excel
> >
> >
> > "Dean" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > | I'm sorry for not noting that. We are talking ordinary EXCEL, not
> > macros.
> > | So, I think I'm still stuck.
> > |
> > | "Niek Otten" <(E-Mail Removed)> wrote in message
> > | news:(E-Mail Removed)...
> > | > Good point; are we talking VBA or worksheet formulas?
> > | >
> > | > --
> > | > Kind regards,
> > | >
> > | > Niek Otten
> > | > Microsoft MVP - Excel
> > | >
> > | > "JLGWhiz" <(E-Mail Removed)> wrote in message
> > | > news:38BE7752-7415-4CCF-A3D2-(E-Mail Removed)...
> > | > | Maybe: Format(B34, "mmmm")
> > | > |
> > | > | "Dean" wrote:
> > | > |
> > | > | > It does show 39233 if formatted as general. Any other ideas?
> > | > | >
> > | > | > "Niek Otten" <(E-Mail Removed)> wrote in message
> > | > | > news:(E-Mail Removed)...
> > | > | > > 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" <(E-Mail Removed)> wrote in message
> > | > | > > news:wPKdnbBc-(E-Mail Removed)...
> > | > | > > | 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
> > | > | > > |
> > | > | > > |
> > | > | > >
> > | > | > >
> > | > | >
> > | > | >
> > | > | >
> > | >
> > | >
> > |
> > |
> >
> >

--

Dave Peterson