# UDF to Calculate the Month

##### Board Regular
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

### Excel Facts

Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

#### Lewiy

##### Well-known Member
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?)

##### Board Regular
yes they will be slightly different for each month

#### Lewiy

##### Well-known Member
Can you post each of the date ranges?

#### Scott Huish

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

##### Board Regular
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

##### MrExcel MVP
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

##### Well-known Member
With that set of data, then something like this perhaps:
Code:
``````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.

##### Board Regular
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

##### MrExcel MVP
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?

Replies
2
Views
196
Replies
4
Views
233
Replies
1
Views
190
Replies
9
Views
212
Replies
4
Views
330

1,191,557
Messages
5,987,269
Members
440,087
Latest member
Ruppert23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back