wrong format for month in cell

[Gerry Viator]

Hi all,

Using Excel 2003 on Windows XP Pro. sp2

I have a date in a cell like this 03/01/2008. let say cell B1
Under format cell; Category = Date , Type = *03/14/2008

In another cell I have a formula like this; =MONTH(B1)
Under format cell; Category = Custom, Type = mmmm
the result = January. what???

I want it to be March, don't understand why it thinks 01 = month?
Not sure why it would not be; 03 = month, 01 = Day, 2008 = year

Will on top of wanting the correct format that I mentioned for month above,
I also need
it be be the previous months name? so the value in the cell would = Feburary
for 03/01/2008

thanks for your help

gv

 

[Ron Coderre]

The MONTH function assumes a DATE (or dateserial number) as it's argument.
The month number of 03/14/2008 equals 3.

That dateserial number (3) is the 3rd day after 31-DEC-1899...03-Jan-1900.
Consequently, the formatting "mmmm" will return January.

Try this in the other cell:
=B1

Since B1 equal 03/14/2008, the cell will display "March".

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Gerry Viator]

thanks for your help and explanation. yes that works.

How do I subtract one month in that formula?

thanks
gv

 

[Ron Coderre]

There are a couple ways to subtract one month from a date...
depending on whether you have the Analysis ToolPak add-in installed.

If yes:
=EDATE(B1,-1)

If no:
=DATE(YEAR(B1),MONTH(B1)-1,DAY(B1))

Here's a good resource for date functions:
http://www.cpearson.com/excel/datearith.htm

Does that help?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Gerry Viator]

I figured it out.

=(B2)-1

thanks
gv

thanks for your help and explanation. yes that works.

How do I subtract one month in that formula?

thanks
gv