Help with formula to find if nth day of month falls between 2 dates.

[meegs]

Hello,

I am having trouble finding a formula that finds whether a specific day of the month falls on or between 2 different dates. I.e. I need to determine if the 12th falls between 4/10 and 4/23, in this case the formula should return a "YES". I have attached a file with a few date ranges as well as a few formulas I've tried that have not worked. Any advise would be much appreciated!

Example Sheet.xlsx

[6StringJazzer]

Please revise your profile. I think you mean Office 365 (which is now called Microsoft 365).

Starting on row 2, copy down

Please Login or Register  to view this content.

It might be possible to come up with one that is more clever and shorter, but this will always work.

This assumes that all pairs of dates are either in the same month or consecutive months. It won't work with 12/19/2022 - 2/5/2023.

[Fluff13]

How about

Please Login or Register  to view this content.

[etaf]

edit - you have a few answers which have appeared while i was working on the solution and did not refresh - so maybe ignore this

as indirect is volatile - and so not great to use if better alternatives as it recalculates all instances every time and can slow spreadsheet down


i have modified this formula
https://www.got-it.ai/solutions/exce...-between-dates
to show the DAY of the month

seems to work
=SUMPRODUCT(--(DAY(ROW(INDIRECT(A2&":"&B2)))=12))

DAY(ROW(INDIRECT(A2&":"&B2)))
created an array of all the days between those dates

then it just see if any are 12
and counts how many there are

add that to an IF()

=IF(SUMPRODUCT(--(DAY(ROW(INDIRECT(A2&":"&B2)))=12))>0, "Yes","No")

i'm assuming your version of excel is 365 and not 360 ????

o array should automatically be created and not require
control+shift+enter to work

[meegs]

Thank you so much, this works!

[meegs]

This works as well, thanks so much!

[meegs]

Great option, gives me what I need. Thanks!!

[Fluff13]

Glad to help & thanks for the feedback.