Forum Discussion
Test if day of month falls between two dates
I have a spreadsheet that projects cash flows by week. Each column represents "The week of [date]" where [date] is the Monday of a week. Each row is a bill, such as rent or a subscription, that is to...
Like this?
=IF( (($B2>=DAY(C$1))+(EOMONTH(C$1,0)+$B2<(C$1+7)))*($B2<(DAY(C$1)+7))*($B2<=DAY(EOMONTH(C$1,0))), $A2,0)
SergeiBaklan I am trying to adapt this formula for weekly dates that are the end of the week as opposed the the beginning of the week. I'm having difficulty understanding the logical test here. I tried changing the <> signs and changing the +7 to -7, but that didn't work. Can you assist?
Quick workaround.
Assuming in the header is Sunday instead of Monday
within existing formula just change everywhere C$1 on C$1-6
=IF(
(($B2 >= DAY(C$1 -6)) + (EOMONTH(C$1-6, 0) + $B2 < (C$1-6 + 7))) *
($B2 < (DAY(C$1-6) + 7)) * ($B2 <= DAY(EOMONTH(C$1-6, 0))),
$A2,
0
)In attached file (third sheet) I did that for the first column only.
If more close to plain English
DEFINE: weekStartDate = columnDate - 6 endOfMonth = EOMONTH(weekStartDate, 0) nextWeekStart = weekStartDate + 7 RETURN: =if ( dayOfMonth >= weekStartDate or dayOfMonth + endOfMonth < nextWeekStart ) and ( dayOfMonth < nextWeekStart and dayOfMonth <= endOfMonth ) then billAmont else 0- SergeiBaklan Thank you so much for the quick response. That did the trick! I wish I understood the logical test, but I'm not following it with the multiple <>=. Any way to explain it verbally? Anyway, your assistance is very much appreciated!!
