which function to use?

[A. Toczko]

I have a column of data which is dates, entered as dd/mm/yy, and formated to
display mmm-yy. I would like to set up another column that will assign a
number for each month shown in the date column, where April is month 1, May
month 2, etc.

Enter data in column 1 as 25/11/05, it displays as Nov-05, and in the second
column, I need a function that will compute to 8, the month number. TIA.

 

[Paul B]

How about =MONTH(A1)

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Use this

=IF(MONTH(A1)<4,MONTH(A1)+9,MONTH(A1)-3)

Where colum A contains the dates.

HTH

J

 

[Guest]

This won't work Paul, as entering 25/11/05 - will compute as the month number
11 rather than 8 (which i believe represents the financial period month)

 

[A. Toczko]

Excellent! Thanks so much.

Use this

=IF(MONTH(A1)<4,MONTH(A1)+9,MONTH(A1)-3)

Where colum A contains the dates.

HTH

J

 

[Peo Sjoblom]

Another way

=INDEX({10;11;12;1;2;3;4;5;6;7;8;9},MONTH(A1))

 

[Harlan Grove]

Another way

=INDEX({10;11;12;1;2;3;4;5;6;7;8;9},MONTH(A1))

....

Brute force!

=MOD(MONTH(A1)-4,12)+1