I am using

=MONTH(H6)

and have the format of the cell set to mm the value is
stuck at 01 and I cannot understand why.

=YEAR(H6) works fine.

Many Thanks

Hi
don't format the cell as MM. just format the cell as 'Number' or
'General'

--
Regards
Frank Kabel
Frankfurt, Germany


Rich wrote:
I am using

=MONTH(H6)

and have the format of the cell set to mm the value is
stuck at 01 and I cannot understand why.

=YEAR(H6) works fine.

Many Thanks

try this
Format Cell Custom 0

----- Rich wrote: ----

I am using

=MONTH(H6

and have the format of the cell set to mm the value is
stuck at 01 and I cannot understand why

=YEAR(H6) works fine

Many Thank

On Sun, 16 May 2004 10:55:31 -0700, "Rich"
wrote:

I am using

=MONTH(H6)

and have the format of the cell set to mm the value is
stuck at 01 and I cannot understand why.

=YEAR(H6) works fine.

Many Thanks

=MONTH(H6) returns a number in the range of 1-12.

When you format the cell as 'mm', Excel thinks you mean a date.

Excel stores dates as serial numbers where 1 = 1 jan 1900 (or 1904).

So when Excel sees a date that is in the range of 1-12, it interprets that as a
date in the range 1 Jan to 12 Jan 1900 (or 1904). So that function will always
return a 1 since the number represents the month of January.

It is only serendipity that YEAR(H6) appears to work. It is probably really
returning a year in the early 1900's.

Suggestion: format the cell as General.


--ron

Hi,

mm is minutes, format to mmm for Month.

Matt


-----Original Message-----
I am using

=MONTH(H6)

and have the format of the cell set to mm the value is
stuck at 01 and I cannot understand why.

=YEAR(H6) works fine.

Many Thanks
.

"Ron Rosenfeld" wrote in message
...
On Sun, 16 May 2004 10:55:31 -0700, "Rich"
wrote:

It is only serendipity that YEAR(H6) appears to work.

I wouldn't call it serendipitous if that was what you were looking for,
fortuitous maybe, but not serendipitous.

On Sun, 16 May 2004 20:56:46 +0100, "Bob Phillips"
wrote:


"Ron Rosenfeld" wrote in message
.. .
On Sun, 16 May 2004 10:55:31 -0700, "Rich"
wrote:

It is only serendipity that YEAR(H6) appears to work.

I wouldn't call it serendipitous if that was what you were looking for,
fortuitous maybe, but not serendipitous.


You can call it either. The first definition of serendipitous includes
fortuitous. (Random House Webster's Unabridged Dictionary V3.0)

serendipitous, adj.
1. come upon or found by accident; fortuitous: serendipitous scientific
discoveries.



--ron

It may include fortuitous, but if it is fortuitously finding what you
expected, where is the happy accident in that?

--

HTH

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

"Ron Rosenfeld" wrote in message
...
On Sun, 16 May 2004 20:56:46 +0100, "Bob Phillips"
wrote:


"Ron Rosenfeld" wrote in message
.. .
On Sun, 16 May 2004 10:55:31 -0700, "Rich"
wrote:

It is only serendipity that YEAR(H6) appears to work.

I wouldn't call it serendipitous if that was what you were looking for,
fortuitous maybe, but not serendipitous.


You can call it either. The first definition of serendipitous includes
fortuitous. (Random House Webster's Unabridged Dictionary V3.0)

serendipitous, adj.
1. come upon or found by accident; fortuitous: serendipitous scientific
discoveries.



--ron

"Rich" skrev i melding
...
=MONTH(H6)
and have the format of the cell set to mm the value is
stuck at 01 and I cannot understand why.
=YEAR(H6) works fine.

It's not very obvious what you expect to see. MONTH returns the month
/number/. YEAR returns the year number -which usually is just fine. If you
want to see "feb" or "oct" then either
=TEXT(H6,"mmm")
for returning the real text, or
=H6
format cell as "mmm", for keeping the date and just display the text.
=MONTH(H6) formatted as mmm will always return jan btw, so son't do that.

HTH. Best wishes Harald

"Rich" skrev i melding
...
I am using

=MONTH(H6)

and have the format of the cell set to mm the value is
stuck at 01 and I cannot understand why.

Sent too quick, sorry. Completed
=MONTH(H6) formatted as mmm will always return jan btw, so son't do that.
The formula will return month number as stated, and dates 1 to 12 is January
1st to january 12th 1900. Which are all in january, month 01.

Could do
=MONTH(H6) * 29
then format as mm

It's rough, but how often does the calendar change?

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Harald Staff" wrote in message
...
"Rich" skrev i melding
...
I am using

=MONTH(H6)

and have the format of the cell set to mm the value is
stuck at 01 and I cannot understand why.

Sent too quick, sorry. Completed
=MONTH(H6) formatted as mmm will always return jan btw, so son't do that.
The formula will return month number as stated, and dates 1 to 12 is
January
1st to january 12th 1900. Which are all in january, month 01.

On Mon, 17 May 2004 07:59:48 +0100, "Bob Phillips"
wrote:

It may include fortuitous, but if it is fortuitously finding what you
expected, where is the happy accident in that?

OIC your point now.

Since he was formatting the MONTH result as a date, I assumed (I know my
assumption may be incorrect), that he was also formatting the YEAR as a date.




--ron

Ron,

I was just picking up on the word, Serendipity is one of my favourites.

Regards

Bob

"Ron Rosenfeld" wrote in message
...
On Mon, 17 May 2004 07:59:48 +0100, "Bob Phillips"
wrote:

It may include fortuitous, but if it is fortuitously finding what you
expected, where is the happy accident in that?

OIC your point now.

Since he was formatting the MONTH result as a date, I assumed (I know my
assumption may be incorrect), that he was also formatting the YEAR as a
date.




--ron