=month() function equivalent for fiscal calendar

[Jon Ratzel]

My company uses a fiscal calendar where the month ends on the 4th Friday of
every month except for the end of the quarters (March, June, Sep, and Dec)
which end on the 5th Friday. I have a column with various dates ranging from
01/01/06 through today and I'd like the fiscal calendar month name (or month
number) to show in the cell to the right of each date. Normally the =month()
function would work, but it can't with our fiscal month.

Can someone help me come up with a formula that I can use that'll serve the
same purpose? I'm trying to avoid a 24 layer nested if statement because the
formula will often be copied 2003 excel.

Thanks, Jon

 

[Jim]

You could use a 2 column table of dates. Col 1 would be month start date and
col 2 month end date. Then use a lookup on the table with TRUE as the last
argument.

The table would be perpetual if you use calcs for everything except the
first start date. You'd just have to change that first date manually once
each year.

HTH,
Jim