Month

R

Rich

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
 
C

chris

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
 
R

Ron Rosenfeld

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
Click to expand...

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

Bob Phillips

Ron Rosenfeld said:
On Sun, 16 May 2004 10:55:31 -0700, "Rich"

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

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

Ron Rosenfeld

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

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
 
B

Bob Phillips

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

Harald Staff

Rich said:
=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.
Click to expand...

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
 
H

Harald Staff

Rich said:
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.
Click to expand...

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

Rob van Gelder

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

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

Ron Rosenfeld

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

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
 
B

Bob Phillips

Ron,

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

Regards

Bob
 
Top