month formula
 

Hi All

A question - if I type in the date 14/12/2004 and in the column next to it
use the month formula with the cell format set to general it correctly
returns 12. However if I want it to show say Dec I went to custom typed in
mmm and it always returns Jan.

Why is this and is there a better way to return the month description rather
than month number.

Thanks

month formula
 

On Fri, 25 Aug 2006 04:44:01 -0700, Reggiee
wrote:

Hi All

A question - if I type in the date 14/12/2004 and in the column next to it
use the month formula with the cell format set to general it correctly
returns 12. However if I want it to show say Dec I went to custom typed in
mmm and it always returns Jan.

Why is this and is there a better way to return the month description rather
than month number.

Thanks


Why?

Excel stores dates as serial numbers with 1 = 1 Jan 1900 (or 1 Jan 1904).

=Month("14 Dec 2004") returns the number 12. Day 12 is equivalent to 12 Jan
1900 so formatting that as a month would return Jan.

To do what you want, with A1: 14/12/2004

B1: =A1
Format/Cells/Number/Custom Type: mmm

or

B1: =TEXT(A1,"mmm")

The first retains the date in B1; the second will result in a text string.


--ron

month formula
 

top man Ron - I did understand about how excel stores dates but nevertheless
i found the fact that it returned the number correctly ok but not the
description somewhat baffling

thanks again

"Ron Rosenfeld" wrote:

On Fri, 25 Aug 2006 04:44:01 -0700, Reggiee
wrote:

Hi All

A question - if I type in the date 14/12/2004 and in the column next to it
use the month formula with the cell format set to general it correctly
returns 12. However if I want it to show say Dec I went to custom typed in
mmm and it always returns Jan.

Why is this and is there a better way to return the month description rather
than month number.

Thanks


Why?

Excel stores dates as serial numbers with 1 = 1 Jan 1900 (or 1 Jan 1904).

=Month("14 Dec 2004") returns the number 12. Day 12 is equivalent to 12 Jan
1900 so formatting that as a month would return Jan.

To do what you want, with A1: 14/12/2004

B1: =A1
Format/Cells/Number/Custom Type: mmm

or

B1: =TEXT(A1,"mmm")

The first retains the date in B1; the second will result in a text string.


--ron

month formula
 

On Fri, 25 Aug 2006 05:55:02 -0700, Reggiee
wrote:

top man Ron - I did understand about how excel stores dates but nevertheless
i found the fact that it returned the number correctly ok but not the
description somewhat baffling

thanks again

You're welcome.

Glad to help.
--ron