Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for calculating 12 month rolling period | Excel Discussion (Misc queries) | |||
Weekday formula calculating to end of month | Excel Worksheet Functions | |||
Formula = Today's date + 1 month | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula for # of sales days in a month? | Excel Worksheet Functions |