Month function returns 1 (Jan) for June

[provincial]

What am I missing

I have a date in A9 (serial 40342 displayed as 13/06/2010 in dd/mm/yyyy format) and in B9 have the formula =MONTH(A9) which returns 1 (or Jan when formatted "mmm").

The desired result of the MONTH() function in B9 is 6 (Jun) of course.

 

[jeffreybrown]

With the serial number 40342, yes the date is 13/06/2010 with a format of dd/mm/yyyy.

The result of =Month(A9) is 6 which to Excel in a date format is 1/6/1900.

Dates start at 1/1/1900 in Excel so a 6 is 1/6/1900.

If you want the 6 to become Jun you could try, =DATE(YEAR(A9),MONTH(A9),1) with your custom format of mmm.

Not sure if this is the most efficient way but at least my attempt.

 

[Norie]

If it's a date in A9 the MONTH function should return 6 not 1.

 

[T. Valko]

What am I missing

I have a date in A9 (serial 40342 displayed as 13/06/2010 in dd/mm/yyyy format) and in B9 have the formula =MONTH(A9) which returns 1 (or Jan when formatted "mmm").

The desired result of the MONTH() function in B9 is 6 (Jun) of course.

Are you sure you're referring to the correct cell with the correct date in it?

 

[Scott Huish]

I see what jeffreybrown is saying because if you are formatting the result of the month formula to get the month name, you are going to get the wrong result.

If you are trying to return the month name:

=A9

and format as mmm

 

[T. Valko]

Are you sure you're referring to the correct cell with the correct date in it?

After reading some more of the replies this is becomming a bit confusing.

Do you want the month NUMBER or the month NAME?

MONTH returns the month NUMBER, a number from 1 to 12.

If you want the month NAME try one of these...

A9 = 13/06/2010

For the short month name: Jun

=TEXT(A9,"mmm")

For the long month name: June

=TEXT(A9,"mmmm")

 

[jeffreybrown]

Thanks Biff...

I knew I had the complicated method...The text format escaped at the time

 

[provincial]

Many thanks to you all

I started a new workbook, entered the date (13/06/2011) and use the month formula in the adjacent cell and it worked (returned 6)!

I returned to my original workbook and rekeyed the date and this worked also. I can only assume my original problem was created from the data having been imported from a quickbooks export.

Once again, thanks

 

[T. Valko]

Many thanks to you all

I started a new workbook, entered the date (13/06/2011) and use the month formula in the adjacent cell and it worked (returned 6)!

I returned to my original workbook and rekeyed the date and this worked also. I can only assume my original problem was created from the data having been imported from a quickbooks export.

Once again, thanks

Ok, good deal. Thanks for letting us know!