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

 

[Lewiy]

Can you clarify all of the month ranges? The 2 examples you have given indicate that they are all going to be different (or is it based on a number of days?)

 

[bradh_nz]

yes they will be slightly different for each month

 

[Lewiy]

Can you post each of the date ranges?

 

[Scott Huish]

Those date ranges also overlap, you have 06/02/07 being both January and February

 

[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

 

[Scott Huish]

Can you post the complete list?
Does leap year need to play a part in this somehow or is it always consistent no matter what the year?

 

[Lewiy]

With that set of data, then something like this perhaps:

Function myMonth(myDate As Date) As Long
If myDate <= DateSerial(2007, 2, 6) And myDate >= DateSerial(2007, 1, 5) Then myMonth = 1
If myDate <= DateSerial(2007, 3, 5) And myDate >= DateSerial(2007, 2, 7) Then myMonth = 2
If myDate <= DateSerial(2007, 4, 8) And myDate >= DateSerial(2007, 3, 6) Then myMonth = 3
End Function

But there may be a tidier way of doing it depending on the date ranges for the rest of the year.

 

[bradh_nz]

05-Mar-07 - 10-Apr-07= March 07
11-Apr-07 - 08-May-07= April 07
09-May-07 -07-Jun-07 = May 07
08-Jun-07 - 06-Jul-07 = June 07
07-Jul-07 -07-Aug-07 = July 07
08-Aug-07 - 07-Sep-07= August 07
08-Sep-07 - 05-Oct-07= September 07
06-Oct-07 - 07-Nov-07= October 07
08-Nov-07 -07-Dec-07= November 07
08-Dec-07 - 08-Jan-08= December 07
09-Jan-08 -07-Feb-08 = January 08
08-Feb-08 - 07-Mar-08= February 08

 

[Scott Huish]

In your first example above January 5, 2007 through February 6, 2007 counted as January but in your complete list it is January 9, 2008 through February 7, 2008. What accounts for the varying dates?