CODEWITHSUNDEEP
exceldaterange
L. Rohr
L. Rohr

Reputation: 11

Check if Day Number falls within date range Excel

I am trying to build a cash flow spreadsheet that contains a column of expenses with a numerical "due date" (day of the month the expense is due) and an amount for that expense.

The sheet also contains 2 rows of dates, Week Start and Week End.

I would like to create a formula that checks to see if Expense Due Date falls between the Week Start and Week End. If true, place the expense in that column. If not, either leave the cell blank (preferred) or set equal to 0.

I've tried several existing formulas, but have not found anything that seems to work.

Attached screenshot shows the layout of what I'm trying to accomplish.

enter image description here

UPDATE! SOLVED

I was able to solve the problem this way. Is it graceful? I don't know! But, I was able to use this formula to make it happen. It checks [Due] against the [Week Start] and [Week End] by parsing out the day (DD) part of the date (MM/DD/YYYY).

=if( mid([Week Start],4,2)<mid([Week End],4,2), 
    if(AND( [Due]<=value(mid([Week End],4,2)), $B37>=value(mid([Week Start],4,2)) ), [Amount],""),
    if(OR( [Due]<=value(mid([Week End],4,2)), $B37>=value(mid([Week Start],4,2)) ), $[Amount],"") 
)

Example with cell references in it:

=if( mid(FI$1,4,2)<mid(FI$2,4,2), 
    if(AND( $B37<=value(mid(FI$2,4,2)), $B37>=value(mid(FI$1,4,2)) ), $C37,""), 
    if(OR( $B37<=value(mid(FI$2,4,2)), $B37>=value(mid(FI$1,4,2)) ), $C37,"")
)

[Due] Syntax -> 00

[Amount] Syntax -> $00.00

[Week Start] -> MM/DD/YYYY

[Week End] -> MM/DD/YYYY

Upvotes: 0

Views: 1565

Answers (3)

SkysLastChance
SkysLastChance

Reputation: 221

I had the same problem. Here is what I came up with.

=IF(AND(D$1<=DATE(YEAR(D$1),IF(DAY(D$1)<=--$B6,MONTH(D$1),MONTH(D$1) + 1),$B6),D$1 + 7 > DATE(YEAR(D$1),IF(DAY(D$1)<=--$B6,MONTH(D$1),MONTH(D$1) + 1),$B6)),$C6,"")

D1 = Start Date

C6 = Amount

B6 = Due Date

1 week

 D$1 + 7

2 weeks

 D$1 + 7 * 2 = 2 weeks

Upvotes: 0

L. Rohr
L. Rohr

Reputation: 11

Well, if there is a smarter way to do this, I don't know what it is. But this appears to work (without rigorous testing yet):

=if(AND(day(D$3)-day(D$2)<6,day($B6+1)<6),$C6,if(AND(day(D$3)>=day($B6+1),day(D$2)<=day($B6+1)),$C6,""))

Upvotes: 0

Mark S.
Mark S.

Reputation: 2584

You may want to adjust the less than or equal to / greater than or equal to, to better suit your needs but here's the formula to add to your table.

=if(and([start Date]<=[Due Date],[End Date]>=[Due Date]),[Expense],0)

Start Date = Reference Start Date Cell

Due Date = Reference Due Date Cell

End Date = Reference End Date Cell

Expense = Reference Expense Cell

Upvotes: 1

Related Questions