Month function yields incorrect solution

[alwein]

I have a worksheet, admittedly of suspect background, it was extracted from
an older one with lots if errors in it. I have mostly cleaned it up and it
works fine except for extracting month from a date field. I have two date
fields in the worksheet and one works just fine. It is formatted as alpha
month, day, and four digit year. I enter a column "=month(g4)" and I get the
correct month. The second date column appears to be formatted exactly the
same and =month gives me a date in January, 1900. So I tried creating a new
date column, formatted the same and entered random dates in it. I then
entered a new "=month" column and I get the dates in January, 1900. So I
opened a new file and tried it again and the month function yields the
correct answer. I suspect the software that created the base file I started
with is pre-Y2K but when I enter dates as, say, 2/3/78, they appear as
February 3, 1978. It does not appear to make any difference if the dates are
before or after 2000. Any ideas on what is happening and how it might be
fixed?

 

[Pete_UK]

Format the cell with the MONTH() formula in as General or Number - it
looks like it has taken the format from the date cell and as it will
be returning a number in the range 1 to 12 this will be displayed as
1st to 12th January 1900.

Hope this helps.

Pete

 

[smartin]

I have a worksheet, admittedly of suspect background, it was extracted from
an older one with lots if errors in it. I have mostly cleaned it up and it
works fine except for extracting month from a date field. I have two date
fields in the worksheet and one works just fine. It is formatted as alpha
month, day, and four digit year. I enter a column "=month(g4)" and I get the
correct month. The second date column appears to be formatted exactly the
same and =month gives me a date in January, 1900.

Are the columns where you are entering =month() picking up an existing
date format (or assuming the same, for whatever reason)? What happens if
you format these columns as general?

So I tried creating a new

 

[Ron Rosenfeld]

The second date column appears to be formatted exactly the
same and =month gives me a date in January, 1900. So I tried creating a new
date column, formatted the same and entered random dates in it. I then
entered a new "=month" column and I get the dates in January, 1900.

The formatting of the column in which you have the =month(cell_ref) formula is
formatted as Date.

Since the =MONTH() formula will return a number from 1-12, if the column is
formatted as a date, Excel will return a date between 1 Jan 1900 and 12 Jan
1900.

If you want to return a month number, format the column as General.
--ron

 

[alwein]

All three responses were more or less the same and all worked, Thanks,