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

 

[bradh_nz]

this is the complete list, these different dates are different reporting periods

 

[Scott Huish]

Based on that, it looks like it changes every year and there isn't anything consistent about it. I would make a LOOKUP table which you would then just change once a year. The first column is the low end of each range. The second column is just a date using the first of each month to represent the corresponding month/year.

Formula in B1:
=LOOKUP(A1,$J$1:$K$12)

Format this cell as mmmm yyyy

Book3
	A	B	C	D	E	F	G	H	I	J	K
1	05/03/2007	March 2007								05/03/2007	01/03/2007
2										11/04/2007	01/04/2007
3										09/05/2007	01/05/2007
4										08/06/2007	01/06/2007
5										07/07/2007	01/07/2007
6										08/08/2007	01/08/2007
7										08/09/2007	01/09/2007
8										06/10/2007	01/10/2007
9										08/11/2007	01/11/2007
10										08/12/2007	01/12/2007
11										09/01/2008	01/01/2008
12										08/02/2008	01/02/2008
Sheet1

 

[bradh_nz]

Thanks Lewiy


Your code worked perfectly

 

[bradh_nz]

man thanks for that HOTPEPPER

how did i miss something so simple