Test if day of month falls between two dates

[AlejandroRuben]

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.

[#2]

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)

[#3]

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). 

[#4]

please check attached file. alternatively you can use below formula

=($B2<=DAY(C$1))*($B2>=(DAY(C$1)-6))*$A2

[#5]

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,...)

[#6]

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.

[#7]

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.

[#8 · Accepted solution]

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)

[#9]

Yes! That does what I want. Thank you so much!

[#10]

You are welcome

[#11]

@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?

[#12]

@dwiseman 

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.

[#13]

@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!!

[#14]

@dwiseman 

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