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 be paid the same amount each month on the same day of each month. COL A contains the amount of the bill, and COL B contains an integer containing the day of the month the bill is to be paid, e.g., "5" for the fifth of each month.
This formula is intended to calculate, for each sell in the range, if this is the week of the month that contains the pay date:
=IF(AND($B2>=DAY(C$1),$B2<DAY(D$1)),$A2,0)
The attached sheet is set up to test every day from 1 to 31. It seems to work with my sample dates, except for dates near month end and month start.
Is there a better and reliable way to do this in Excel?
Thank you in advance for your help with this.
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)
13 Replies
Hi,
That could be
=IF( ($B2>=DAY(C$1))*($B2<(DAY(C$1)+7))*($B2<=DAY(EOMONTH(C$1,0))), $A2,0)
(second sheet attached)
Thanks for your quick response! The solution in your attached sheet still fails with the low numbered days (see weeks of 12/31/18, 1/28/19, 3/25/19).
Yes, but I'm not sure what is the logic of the table for such dates. If Pay Day = 1 for the Feb 01 which column shall we use - which starts from Feb 04 (next to week from Jan 28), or in first cell of week from Jan 28, or columns will be prolonged down (30,31,1,2,...)
