Determine if a month falls between 2 dates

[Welsrot]

I'm trying to determine if a month falls between 2 dates and return the number in column E. For example cell N3 would return the value in cell E3(5) if jan is between the dates in cell F3 and G3. The second example is what I want it to look like so I can calculate how many people per month I need.

 

[Special-K99]

As you only have months in N2:Y2, you're implying there will NEVER be a year crossover in date range in columns F and G, ie you won't have something like 01-Dec-23 to 01-Mar-24.
If there is a year change then a normal month comparison won't work, is Dec (12) > Dec (12) and less than Mar (03).
If the date range changes years then youll have to include the year in your range N2:Y2.

 

[Welsrot]

As you only have months in N2:Y2, you're implying there will NEVER be a year crossover in date range in columns F and G, ie you won't have something like 01-Dec-23 to 01-Mar-24.
If there is a year change then a normal month comparison won't work, is Dec (12) > Dec (12) and less than Mar (03).
If the date range changes years then youll have to include the year in your range N2:Y2.

I'll include the year thats not an issue as the dates will cross years. This is basically just an example as I can not figure out how to accomplish this without creating many columns and breaking out the date.

 

[Rick Rothstein]

If cell N3 contains the date (with year) that you want to test is between the dates (with years) in cell F3 (the beginning date) and cell G3 (the ending date), then this formula will return TRUE if it is between, but not equal to, two specified dates...

=MEDIAN(N1,F3+1,G3-1)=K1

If the test date can equal either of the two surrounding dates as well as be between them, then remove the +1 and -1 from my formula. And, of course, you can drop the equal sign and use the rest as the logical test in an IF function call if desired.

 

[Joe4]

If your Start Date and Duration were always in the same year, you could do something like this:

Formula in N2, copied down and across:

=LET(m,MONTH(DATEVALUE(N$1 & " 1, 2024")),IF(AND(m>=MONTH($F2),m<=MONTH($G2)),$E2,0))

I am not sure what, if anything, you wanted to do with lines like 4 and 5 in your example, where there is no duration date.
You do not show anything for those rows in your expected output.