Month

[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

 

[Frank Kabel]

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

 

[Guest]

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

 

[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

=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

 

[Matt Lunn]

Hi,

mm is minutes, format to mmm for Month.

Matt

 

[Bob Phillips]

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

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.

 

[Ron Rosenfeld]

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

 

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

 

[Harald Staff]

=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

 

[Harald Staff]

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.

 

[Rob van Gelder]

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

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

 

[Ron Rosenfeld]

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

 

[Bob Phillips]

Ron,

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

Regards

Bob