Forum Discussion
Solved
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 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!!
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