Appreciate advice in advance..

I have set calendar dates equating to a Reporting Month ie

01/01/06 - 31/01/06 = M01
01/02/06 - 28/02/06 = M02

now if I have a cell that relates to when a contract closes (eg 15/01/06)
how can I set the formula in next cell to equal the "M01"

Will need to run this out for the whole year, so not sure I can build that
many < nested IF functions

Thanks

Shaun,
maybe you can try it another way using the VLOOKUP function rather than the
IF function. It's much simpler. Create a 2 column table in the spreadsheet.
On the left you list the first day of each month and on the right you list
your output, i.e. M01, M02 etc. one below the other as shown (continue to the
end of the year);

A B
1 1/1/2006 M01
2 2/1/2006 M02
3 3/1/2006 M03
4 4/1/2006 M04
5 5/1/2006 M05

Put your contract date in G1 (or any other cell) then input this formula
anywhere;
=VLOOKUP(G1,A1:B5,2,TRUE)
The output should be the period you're looking for.
Hope it helps. Cheerio
--
Creator


"Shaun" wrote:

Appreciate advice in advance..

I have set calendar dates equating to a Reporting Month ie

01/01/06 - 31/01/06 = M01
01/02/06 - 28/02/06 = M02

now if I have a cell that relates to when a contract closes (eg 15/01/06)
how can I set the formula in next cell to equal the "M01"

Will need to run this out for the whole year, so not sure I can build that
many < nested IF functions

Thanks

Assume your date is in A2, add this formula to B2:

=MONTH(A2)

Click on the cell then Format | Cell | Number (tab) then Custom and
enter "MO"0 and click OK. You can copy this down for as many dates as
you have in column A.

Hope this helps.

Pete

Sorry, I misread your posting. Same formula, just custom format with
"M"00.

Alternatively, you could have this formula in B2:

="M"&TEXT(MONTH(A2),"00")

and copy down.

Hope this helps further.

Pete

...or even..

=TEXT(A2,"\Mmm")


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=511917