Formula help needed

[zilla37]

I am working with dates that need to have the fiscal calendar period generated beside the date column.
I am just not sure of the formula, I have been trying and cant seem to solve it.

( formula would be in cells C2 to C37 )

For example if the dates in cell B2 to B37 are between 04/01/2009 to 04/28/2009 place the result as 1. ( 1 would = the period number )

if the dates in cell B2 to B37 are between 05/01/2009 to 05/30/2009 place the result as 2.

And so on, would this be one long formula as you have to enter if's for all of the fiscal periods?
Is there a way to join the dates in cells instead of separate cells?

Any help would be appreciated.

http://imageshack.us/photo/my-images/269/24240990.png/

link to file

http://www.megaupload.com/?d=2T7BV8WL

 

[asad]

In C2 insert:

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

and drag down to C37.
See how you go with this.

 

[Ralajer]

If you are willing to rearrange the way you organize your Period Range you can solve your problem with the Vlookup function. Below is an example of how to reorganize the dates. I added bounds to the Period range so the return value would be clear not just N/A's

Excel Workbook
	B	C	D	E
1	Picked Date	Period	Period Range	Period number
2	Mar 31, 2009	Before Periods	Jan 1, 1900	Before Periods
3	Apr 1, 2009	1	Apr 1, 2009	1
4	Apr 28, 2009	1	Apr 29, 2009	Between Periods
5	Apr 30, 2009	Between Periods	May 1, 2009	2
6	May 19, 2009	2	May 31, 2009	After Periods
Sheet1

Excel 2010

Cell Formulas
Range	Formula
C2	=VLOOKUP(B2,$D$2:$E$6,2,TRUE)

Table Created With MrExcel HTML Maker

The lookup table will have to be sorted ascending, chronological in this case, for the above to work.