ExcelBanter - Date formatting problems

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Date formatting problems (https://www.excelbanter.com/excel-programming/388871-date-formatting-problems.html)

Dean[_8_]

Date formatting problems

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

Niek Otten

Date formatting problems

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
|
|

Dean[_8_]

Date formatting problems

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
|
|

JLGWhiz

Date formatting problems

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
|
|

JLGWhiz

Date formatting problems

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
|
|

Niek Otten

Date formatting problems

Dean[_8_]

Date formatting problems

Niek Otten

Date formatting problems

Dean[_8_]

Date formatting problems

Dave Peterson

Date formatting problems

=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" wrote in message
...
<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" wrote in message
...
| 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
| | |
| | |
| |
| |
| |
| |
| |
|
|
|
|

--

Dave Peterson

Dean[_8_]

Date formatting problems

I feel pretty stupid, right about now!

Thanks much Dave!
Dean

"Dave Peterson" wrote in message
...
=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" wrote in message
...
<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" wrote in message
...
| 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
| | |
| | |
| |
| |
| |
| |
| |
|
|
|
|

--

Dave Peterson

All times are GMT +1. The time now is 11:59 AM.