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
|