Formating MONTH() integer

In the MONTH() function, I can get the integer returned OK,
but when I format that cell with "mmm" or "MMMM", it always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
In the MONTH() function, I can get the integer returned OK,
but when I format that cell with "mmm" or "MMMM", it always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?

Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

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

it treats MONTH(date) as a date, which will be sometim in Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
In the MONTH() function, I can get the integer returned OK,
but when I format that cell with "mmm" or "MMMM", it always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?

Yep, I got that far, but I need the MONTH() integer to
calculate other cells. I want the TEXT to sho in the cell,
but I need the integer result for other calcs.

-----Original Message-----
Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

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

it treats MONTH(date) as a date, which will be sometim in
Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
In the MONTH() function, I can get the integer returned OK,
but when I format that cell with "mmm" or "MMMM", it always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?


.

Then use

=CHOOSE(MONTH(date),"Jan", "Feb", "Mar", etc. )

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
Yep, I got that far, but I need the MONTH() integer to
calculate other cells. I want the TEXT to sho in the cell,
but I need the integer result for other calcs.

-----Original Message-----
Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

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

it treats MONTH(date) as a date, which will be sometim in
Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
In the MONTH() function, I can get the integer returned OK,
but when I format that cell with "mmm" or "MMMM", it always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?


.

You can't, at least not directly.

Can't you use the format mmmm and then wrap it in month
in the other calculations,






--

Regards,

Peo Sjoblom


"Dan" wrote in message
...
Yep, I got that far, but I need the MONTH() integer to
calculate other cells. I want the TEXT to sho in the cell,
but I need the integer result for other calcs.

-----Original Message-----
Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

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

it treats MONTH(date) as a date, which will be sometim in
Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
In the MONTH() function, I can get the integer returned OK,
but when I format that cell with "mmm" or "MMMM", it always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?


.

That still doesn't return the month integer (1 to 12).
That's the number I need for other calcs. I still want
that cell to sho the text month (Jan etc.) but I need that
integer. With WEEKDAY func. I keep the integer of the cell
but format it with "dddd" from the format menu. That does
not work with the "mmm" format using the MONTH integer.

Thanks for all your help, but I'm thinking that it just
can't be done. All the best.


-----Original Message-----
Then use

=CHOOSE(MONTH(date),"Jan", "Feb", "Mar", etc. )

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
Yep, I got that far, but I need the MONTH() integer to
calculate other cells. I want the TEXT to sho in the cell,
but I need the integer result for other calcs.

-----Original Message-----
Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

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

it treats MONTH(date) as a date, which will be sometim in
Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in
message
...
In the MONTH() function, I can get the integer
returned OK,
but when I format that cell with "mmm" or "MMMM", it
always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I
reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?


.



.

You can't both have the cake and eat it! Just format the cell as mmm
and then in whatever formula you are using wrap it in the month function.

Assume the cell is in A2, just use custom format mmm,
now let's say you want to use the integer part to count January dates

=SUMPRODUCT(--(MONTH(A2:A100)=1),--(ISNUMBER(A2:A100)))

--

Regards,

Peo Sjoblom


wrote in message
...
That still doesn't return the month integer (1 to 12).
That's the number I need for other calcs. I still want
that cell to sho the text month (Jan etc.) but I need that
integer. With WEEKDAY func. I keep the integer of the cell
but format it with "dddd" from the format menu. That does
not work with the "mmm" format using the MONTH integer.

Thanks for all your help, but I'm thinking that it just
can't be done. All the best.


-----Original Message-----
Then use

=CHOOSE(MONTH(date),"Jan", "Feb", "Mar", etc. )

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
Yep, I got that far, but I need the MONTH() integer to
calculate other cells. I want the TEXT to sho in the cell,
but I need the integer result for other calcs.

-----Original Message-----
Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

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

it treats MONTH(date) as a date, which will be sometim in
Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in
message
...
In the MONTH() function, I can get the integer
returned OK,
but when I format that cell with "mmm" or "MMMM", it
always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I
reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?


.



.

Exactly!

Bob

"Peo Sjoblom" wrote in message
...
You can't both have the cake and eat it! Just format the cell as mmm
and then in whatever formula you are using wrap it in the month function.

Assume the cell is in A2, just use custom format mmm,
now let's say you want to use the integer part to count January dates

=SUMPRODUCT(--(MONTH(A2:A100)=1),--(ISNUMBER(A2:A100)))

--

Regards,

Peo Sjoblom


wrote in message
...
That still doesn't return the month integer (1 to 12).
That's the number I need for other calcs. I still want
that cell to sho the text month (Jan etc.) but I need that
integer. With WEEKDAY func. I keep the integer of the cell
but format it with "dddd" from the format menu. That does
not work with the "mmm" format using the MONTH integer.

Thanks for all your help, but I'm thinking that it just
can't be done. All the best.


-----Original Message-----
Then use

=CHOOSE(MONTH(date),"Jan", "Feb", "Mar", etc. )

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in message
...
Yep, I got that far, but I need the MONTH() integer to
calculate other cells. I want the TEXT to sho in the cell,
but I need the integer result for other calcs.

-----Original Message-----
Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

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

it treats MONTH(date) as a date, which will be sometim in
Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in
message
...
In the MONTH() function, I can get the integer
returned OK,
but when I format that cell with "mmm" or "MMMM", it
always
returns "Jan" or "January" no matter what the integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I
reference
this cell integer to other cells, and the TEXT function
drops the integer. Any way around this?


.



.

Thanks, but... I'm a little lost here. If you mean using
the mmm from the format menu, then it doesn't work. Always
returns January no matter what. And the example you posted
is above my pay grade. I don't quite follow it. Sorry.
But thanks anyway.

-----Original Message-----
You can't both have the cake and eat it! Just format the
cell as mmm
and then in whatever formula you are using wrap it in the
month function.

Assume the cell is in A2, just use custom format mmm,
now let's say you want to use the integer part to count
January dates

=SUMPRODUCT(--(MONTH(A2:A100)=1),--(ISNUMBER(A2:A100)))

--

Regards,

Peo Sjoblom


wrote in message
...
That still doesn't return the month integer (1 to 12).
That's the number I need for other calcs. I still want
that cell to sho the text month (Jan etc.) but I need that
integer. With WEEKDAY func. I keep the integer of the cell
but format it with "dddd" from the format menu. That does
not work with the "mmm" format using the MONTH integer.

Thanks for all your help, but I'm thinking that it just
can't be done. All the best.


-----Original Message-----
Then use

=CHOOSE(MONTH(date),"Jan", "Feb", "Mar", etc. )

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Dan" wrote in
message
...
Yep, I got that far, but I need the MONTH() integer to
calculate other cells. I want the TEXT to sho in the
cell,
but I need the integer result for other calcs.

-----Original Message-----
Dan,

To get the true month, you need to use

=TEXT(date,"mmm")

If you use

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

it treats MONTH(date) as a date, which will be
sometim in
Jan 1900.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing
direct)

"Dan" wrote in
message
...
In the MONTH() function, I can get the integer
returned OK,
but when I format that cell with "mmm" or "MMMM", it
always
returns "Jan" or "January" no matter what the
integer is.
I can get arount this by using the TEXT(cellnum,"mmm")
function (ref. MS knowledge base #213429), but I
reference
this cell integer to other cells, and the TEXT
function
drops the integer. Any way around this?


.



.



.