MONTH

  • Thread starter Thread starter Connie Martin
  • Start date Start date
C

Connie Martin

I used the formula =MONTH(H2) but it's giving me 'Jan' whereas it should be
giving me 'Mar'. The column with the dates is formated as date. What should
my formula be? I also need one for YEAR. Thank you. Connie
 
MONTH should actually return a number between 1 and 12, but remember
that some formats use the month first and others place day first.
There is a YEAR function as well.
 
.. =MONTH(H2)
will return month as a number,
eg; 3 if H2 contains a real date for the month of March

If you want it returned as "Mar", use: =TEXT(H2,"mmm")
If you want the year returned as say: 2008, use: =TEXT(H2,"yyyy")

But from what you posted, I suspect that the "dates" in your col H are not
real dates. Suggest you try this to convert it all at one go to real dates.
Select col H, click Data > Text to Columns, click Next > Next. In step 3,
check "Date", then select the appropriate date format from the droplist, eg:
MDY, then click Finish
 
You can either just link to the date like

=A2

then use a custom format of mmm

or you can use


=TEXT(A2,"mmm")


MONTH will return a number and for March it would return 3 which equal
01/03/1900
thus it would return January



--


Regards,


Peo Sjoblom
 
Thank you everyone. The simple =H2 works. I don't know why =MONTH(H2) was
giving me 'Jan' because the date in the column was 30-Mar-99, and it was
formatted as a date, therefore it should've given me Mar or 3, whichever, but
I was getting Jan or 1. Made no sense. Even when I do =H2 I only get Mar,
so I don't have to format the column. This spreadsheet was downloaded from
SAP, but like I said I formatted the column to date format. Anyway, as long
as I've got the result I want, that's great! Thank you!
 
As I said dates in Excel are serial numbers where one day equals 1 and they
start on Jan 0 1900 which means your MONTH formula returns 3 thus Excel see
it as the 3rd of January 1900 so it makes sense it will return Jan

--


Regards,


Peo Sjoblom
 
Back
Top