Forum Discussion
Test if day of month falls between two dates
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)
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).
please check attached file. alternatively you can use below formula
=($B2<=DAY(C$1))*($B2>=(DAY(C$1)-6))*$A2
Thank you, Erol.
Your sheet is only returning the first monthly due date. I need the sheet to return every monthly due date in the appropriate week.
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,...)
We should use whichever week contains the due date. In the case of Feb 1, it should be the week of Jan 28 because the week of Jan 28 contains:
Jan 28 - Monday
Jan 29 - Tuesday
Jan 30 - Wednesday
Jan 31 - Thursday
Feb 1 - Friday
Feb 2 - Saturday
Feb 3 - Sunday
I am not concerned with weekends vs weekdays, but only which week the pay date falls into each month.
