UDF to Calculate the Month

[bradh_nz]

Hi

I am after a UDF to calculate the month based on a a date. I cannot use the month function in excel as the dates are slighlty different.

ie

05/01/07 - 06/02/07 = Jan
06/02/07 - 05/03/07 = Feb

Any help appreciated

Thanks

[Bob Phillips]

Apart from the fact that your data doesn't make sense (06/02 is Jan and Feb according to that, and you are using actual month limits), but you can use Month

=MONTH(A1-4)

adjust the 4 to get exactly what you mean even if it is not what you asked for.

[bradh_nz]

Apoligies should be
05/01/07 - 06/02/07 = Jan
07/02/07 - 05/03/07 = Feb
06/03/07 - 08/04/07 = March

These will change for each month of the year

[Bob Phillips]

This is the formula for the first 3 months

=MONTH(A1-CHOOSE(MONTH(A1),4,6,5))

You should be able to work out the pattern, extend for the other 9 months.