SOLVED

# Test if day of month falls between two dates

Copper Contributor

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

13 Replies

# Re: Test if day of month falls between two dates

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)

# Re: Test if day of month falls between two dates

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

# Re: Test if day of month falls between two dates

please check attached file. alternatively you can use below formula

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

# Re: Test if day of month falls between two dates

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

# Re: Test if day of month falls between two dates

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.

# Re: Test if day of month falls between two dates

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.

best response confirmed by AlejandroRuben (Copper Contributor)
Solution

# Re: 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)`

# Re: Test if day of month falls between two dates

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

You are welcome

# Re: Test if day of month falls between two dates

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

# Re: Test if day of month falls between two dates

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.

# Re: Test if day of month falls between two dates

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

# Re: Test if day of month falls between two dates

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
``````
1 best response

Accepted Solutions
best response confirmed by AlejandroRuben (Copper Contributor)
Solution

# Re: 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)`