month formula

[Reggiee]

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

 

[Ron Rosenfeld]

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

 

[Reggiee]

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]

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