Defining a Range based on Month value of cells

[tallandpoofy]

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.

 

[Jonmo1]

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

 

[tallandpoofy]

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!

 

[Jonmo1]

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.