=month doesn't work?

[OLY]

I'm in Excel 2010 and trying to extract the month from a column of
dates formatted to look like 1/2/2008

Using the =MONTH() function, I get the following: 1/1/1900. Why isn't
this working? What do I need to do?

Much obliged for any help on this.

AO

 

[Victor Delta]

I'm in Excel 2010 and trying to extract the month from a column of
dates formatted to look like 1/2/2008

Using the =MONTH() function, I get the following: 1/1/1900. Why isn't
this working? What do I need to do?

Much obliged for any help on this.

AO

The cell containing the formula is formatted for dates, If you change it to
integer formatting, it will show 1, and mmm or mmmm formatting will show Jan
or January respectively - which I guess is what you want?

Hope this helps,

V

 

[Ron Rosenfeld]

I'm in Excel 2010 and trying to extract the month from a column of
dates formatted to look like 1/2/2008

Using the =MONTH() function, I get the following: 1/1/1900. Why isn't
this working? What do I need to do?

Much obliged for any help on this.

AO

From HELP:

MONTH function

Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).

Since your cell where you have the formula is formatted to show DATE, it is interpreting the values returned by the Month function as a date. The value 1 = 1 Jan 1900; 2 = 2 Jan 1900; etc.

To see the integers, 1 to 12, representing the months, you must format the cell containing =MONTH() as General, or as Number.

If you want to see something else, post back with your requirements.

 

[joeu2004]

The cell containing the formula is formatted for dates,
If you change it to integer formatting, it will show 1,
and mmm or mmmm formatting will show Jan or January
respectively - which I guess is what you want?

This is only half right.

The MONTH function returns the month number as an integer. Yes, it
displayed as 1/1/1900 because the cell was formatted as Date (or some
similar format). But generally, it would not display the month name with
when formatted as mmm or mmmm. It works with January only by coincidence.
Try the date Feb 13, 2008 (month 2). It will also display Jan or January.

If all you want is to __display__ the month name or even the month number,
if the original date is in A1, simply put =A1 into B1 (for example) and use
the Custom format m, mmm or mmmm.

But keep in mind that B1 still contains the entire date; for example, the
number 39481 for the date Feb 13, 2008.

Use the MONTH function when you truly want a month number between 1 and 12.

 

[Puppet_Sock]

This is only half right.

The MONTH function returns the month number as an integer.  Yes, it
displayed as 1/1/1900 because the cell was formatted as Date (or some
similar format).  But generally, it would not display the month name with
when formatted as mmm or mmmm.  It works with January only by coincidence.
Try the date Feb 13, 2008 (month 2).  It will also display Jan or January.

[snips]

This is because, when you format the cell as mmm, it interprets the
contents of the cell as a date, then displays the name of the month
in that date. Since the content of the cell is 1 or 2 in the two
examples
you give, and since dates in Excel are based on days since 1/1/1900,
a value of 2 is 2/1/1900, still January. Indeed, the process suggested
by victor cannot give anything other than January.

I hate doing dates. It should be a simple thing but I always get it
wrong the first couple times. Then some "bright light" swaps
dd/mm/yyyy for mm/dd/yyyy and everthing is hosed. I've had
both of those in one data file. Annoying!
Socks

 

[Victor Delta]

This is only half right.

The MONTH function returns the month number as an integer. Yes, it
displayed as 1/1/1900 because the cell was formatted as Date (or some
similar format). But generally, it would not display the month name with
when formatted as mmm or mmmm. It works with January only by coincidence.
Try the date Feb 13, 2008 (month 2). It will also display Jan or January.

[snips]

This is because, when you format the cell as mmm, it interprets the
contents of the cell as a date, then displays the name of the month
in that date. Since the content of the cell is 1 or 2 in the two
examples
you give, and since dates in Excel are based on days since 1/1/1900,
a value of 2 is 2/1/1900, still January. Indeed, the process suggested
by victor cannot give anything other than January.

I hate doing dates. It should be a simple thing but I always get it
wrong the first couple times. Then some "bright light" swaps
dd/mm/yyyy for mm/dd/yyyy and everthing is hosed. I've had
both of those in one data file. Annoying!
Socks