Help with Excel formula checking if date is within date range.

[9tanstaafl9]

I'm sure there is probably an easy way to do this, but I can't wrap my head around it.

Say the beginning date in E4 = 2/2/14, the ending date in F4 = 4/5/14. I have columns headings listing the beginning and ending dates of each month, where L1 = 1/1/14 and L2 = 1/31/14, M1 = 2/1/14 etc.

I need a formula that will return the value of column K if any date in the range falls within the month above. So in this example, for row 4, the value would display in Feb (col M), March (col N) and April (col O).

How would I do that?

Right now I have this:

=IF(AND([@strdte]>=L$1,[@strdte]<=L$2)=TRUE,[@Value],0)

But that only gives me the starting month.

Any help appreciated. I am normally fairly smart but this is just stumping me.

 

[9tanstaafl9]

NEVERMIND. I found the answer while I was waiting for a reply. It seems I wanted to search for a formula to find if two date ranges overlap or not. I found it not on this website so I'm not sure if it's kosher to post the link or not.

<code>I tried to post the formula but it keeps getting chopped off. Not sure what is going on.

<code><a,b<x),"do not="" overlap","overlap")<="" strong=""></a,b<x),"do></code></code><code></code>

 

[WarPigl3t]

You confused me. Try adding a table to help explain this.[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Begin
[/TD]
[TD]End
[/TD]
[TD][/TD]
[TD]1/1/2014
[/TD]
[TD]2/1/2014
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1/31/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]2/2/2014
[/TD]
[TD]4/5/2014
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
That's all i could piece together from your explanation.

 

[9tanstaafl9]

I'm so sorry for confusing you and even more for finding the answer while you were trying to help me. Thank you for taking the time to map out my badly worded question. You did get it right. So if K4 was 1000 I would want 1000 to appear in M4, N4 and O4.

Anyway, I found something that worked. It keeps getting truncated when I try to post however. Basically it started like this = if(OR(<l$1,l$2<[@strdte]),0,[@value])
<l$1,l$2<[@strdte]),0,[@value])

<l$1,l$2<[@strdte]),0,[@value])[ quote]
Thanks again!

You confused me. Try adding a table to help explain this.[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N
[/TD]
[TD="align: center"]O
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Begin[/TD]
[TD]End[/TD]
[TD][/TD]
[TD]1/1/2014[/TD]
[TD]2/1/2014[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1/31/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]2/2/2014[/TD]
[TD]4/5/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
That's all i could piece together from your explanation.

</l$1,l$2<[@strdte]),0,[@value])[></l$1,l$2<[@strdte]),0,[@value])
</l$1,l$2<[@strdte]),0,[@value])>