Date calculation using a range of dates

[govitz]

I know I should be able to do this with Excel, but can never get this to work. It involves 2 worksheets within a workbook. The first sheet contains dates and I want it to return the fiscal month for the date. The second sheet contains the table for the fiscal months. The second sheet is to use the comparison of greater than the first date and less than the second date. Please see my examples below.

WORKSHEET 1 - DATES
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]DATE[/TD]
[TD]MONTH[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]03/23/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]01/03/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]04/05/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]03/31/2016[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

WORKSHEET 2 - FISCAL MONTHS
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]MONTH[/TD]
[TD]BEGIN[/TD]
[TD]END[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]12/31/2015[/TD]
[TD]01/31/2016[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]01/30/2016[/TD]
[TD]02/28/2016[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3[/TD]
[TD]02/27/2016[/TD]
[TD]03/27/2016[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4[/TD]
[TD]03/26/2016[/TD]
[TD]05/01/2016[/TD]
[/TR]
</tbody>[/TABLE]

I would expect the results column B (Month) to return in Worksheet 1 - Dates to be:
03/23/2016 = 3
01/03/2016 = 1
04/05/2016 = 4
03/31/2016 = 4

How can I accomplish this in Excel? I am using Excel 2003 (yes, I know it's old).

Thanks,
Govitz

 

[govitz]

Not entirely sure where that formula came from as it doesn't appear to ever have been suggested.

I M2 on the Data sheet, try -->> =LOOKUP(H2,YR!$C$2:$C$13,YR!$B$2:$B$13)

Thanks Jeff, but this only works if the date is exactly what is in YR worksheet column C. I could make this work if I entered every date of the year and what month it falls into.

I was thinking to make the data table simpler by checking a date range beginning with column C and ending with column D (if H2 >YR!C2:C13 AND <YR!D2:D13 then return YR!B2:B13). I don't know if I'm making sense, but it seems I need an array. I just can't get the correct function/syntax to make it work.

 

[jeffreybrown]

Thanks Jeff, but this only works if the date is exactly what is in YR worksheet column C. I could make this work if I entered every date of the year and what month it falls into.

I was thinking to make the data table simpler by checking a date range beginning with column C and ending with column D (if H2 >YR!C2:C13 AND <YR!D2:D13 then return YR!B2:B13). I don't know if I'm making sense, but it seems I need an array. I just can't get the correct function/syntax to make it work.

Can you show an example of where you don't get the right answer?

Lookup does exactly what you are describing, so without seeing some data and what you expect, I'm not following.

We do not need to check column D with the lookup as we only need to see the lower bound date.

Excel LOOKUP Function

 

[Noodleski]

Another option, provided there are no overlapping dates would be (with the OP as example table)

sumproduct(--(A2 < worksheet2$C$2:$C$5),--(A2<worksheet2$c$2:$c5),--(a2> > worksheet2$B$2:$B$5),worksheet2$A$2:$A5)

adjust ranges as needed.

edit: ****... forgot about the < or > glitch
</worksheet2$c$2:$c5),--(a2>