# Defining a Range based on Month value of cells

#### tallandpoofy

##### New Member
Hi All,

I feel like this is either really simple and can be done in the name manager, or requires a somewhat complex macro.

I have a table where column B is Date. I want to define a range within "Table1[Date]" that contains all "March" Cells.

Range Name: "March"
Abstract statement: include in range all cells in Table1[Date] that have month(cell)=3

The ultimate goal is to get this formula:
=Offset(March,-3,2)/offset(March,0,1) where march is the range explained above

I can attach part of the workbook if necessary.

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Welcome to the board..

Assuming your dates are sorted in ascending order, and are all in the same year (assuming current year)

Try something like
=INDEX(A:A,MATCH(DATE(2013,3,0),A:A)+1):INDEX(A:A,MATCH(DATE(2013,4,0),A:A))

Note that this formula will return #VALUE! if entered by itself, because it's a multicell range.
You need to actually DO something with it, like COUNT
=COUNT(INDEX(A:A,MATCH(DATE(2013,3,0),A:A)+1):INDEX(A:A,MATCH(DATE(2013,4,0),A:A)))

Thanks for the reply! I have been lurking here for so long, I didn't even realize it is my first post!

the issue with that formula is that the Date function references a particular date serial number. I am trying to include all dates in the same month in the range.

But using the MATCH function with the INDEX should help...i am trying a few things now.

Thanks!

It actually references 2 dates.
You said you wanted all March..
so I got the last date in February DATE(2013,3,0), and the last date in March DATE(2013,4,0)
I added 1 to the match of the last day in feb.
So the range goes from the last date in Feb +1 to the last date in march.

Again, it's assuming your dates are sorted ascending.

Replies
4
Views
395
Replies
1
Views
472
Replies
5
Views
464
Replies
2
Views
200
Replies
8
Views
419

1,207,108
Messages
6,076,590
Members
446,215
Latest member
userds5593

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