Check if a Certain day of the week falls between two dates

**cwang213** · 07-09-2013, 01:13 PM

Hi all,

First-time poster but long-time lurker. I am trying to find a formula that will check if a Tuesday falls between two dates I have inputted in excel cells.

So, for instance, cell A1 = 7/8/2013 (Monday); cell b1 = 7-10-2013 (Wednesday).

I am trying to create a logical test; something along the lines of "If the days elapsed between cell a1 and cell b1 contain a Tuesday, then X."

Thank you in advance for any help.

**protonLeah** · 07-09-2013, 01:54 PM

Please Login or Register  to view this content.

**cwang213** · 07-09-2013, 04:48 PM

The statement is always returning false. Please advise if something is incorrect. Sample workbook attached. Cell with your formula is highlighted in yellow.

07-09-CWsample.xlsx

**dipique** · 07-09-2013, 05:31 PM

This isn't the prettiest formula I've ever written, but it works... feel free to clean it up for me

=(C1-B1)>=(2+IF(WEEKDAY(B1)=1,1,IF(WEEKDAY(B1)=2,0,8-WEEKDAY(B1))))

Dan

**protonLeah** · 07-09-2013, 05:36 PM

Please Login or Register  to view this content.

**cwang213** · 07-09-2013, 05:46 PM

@dipique: Your formula works great. Thank you. My formulas now, though, are quickly becoming a parenthetical nightmare.

@protonLeah: In the workbook you attached, if you change the date to 6/17, the test still returns true; and, if you change the date to the 20, it'll return false. Thank you for your attention to this, though.

**dipique** · 07-09-2013, 05:50 PM

Haha, sorry about that. I really think it could be better, but I'm a little fried today. I did manage one minor reduction:

=(C1-B1)>=IF(WEEKDAY(B1)=1,3,IF(WEEKDAY(B1)=2,2,10-WEEKDAY(B1)))

That kills one parenthesis. I'm basically saying, "the difference between the two dates needs to be at least X day based on the day of the week.).

Dan

**daddylonglegs** · 07-10-2013, 08:51 AM

Originally Posted by dipique

=(C1-B1)>=IF(WEEKDAY(B1)=1,3,IF(WEEKDAY(B1)=2,2,10-WEEKDAY(B1)))

Doesn't this have to work when B1 and C1 contain dates and times (as per example)?

If B1 is Sunday 7th July 2013 at 18:00 and C1 is Wednesday 10th July 2013 at 10:00 then formula gives FALSE when it should be TRUE

I'm not clear what should happen if the start date or end date is a Tuesday - assuming you want TRUE if any part of a Tuesday is included in the range then you can use this version

=WEEKDAY(B1-3)+INT(C1)-INT(B1)>6

change the 3 depending on the day to check (1=Sun through to 7 for Sat)

....or you can get the same result using NETWORKDAYS.INTL function in excel 2010, i.e.

=NETWORKDAYS.INTL(B1,C1,"1011111")>0

zero at second position in "1011111" indicates Tuesday, move the zero for other days

**Teethless mama** · 07-10-2013, 09:22 AM

Try this:

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(INT(B1)&":"&INT(C1))))=3))>0

Check if a Certain day of the week falls between two dates

LinkBack

Thread Tools

Rate This Thread

Display

Check if a Certain day of the week falls between two dates

Re: Check if a Certain day of the week falls between two dates

Re: Check if a Certain day of the week falls between two dates

Re: Check if a Certain day of the week falls between two dates

Re: Check if a Certain day of the week falls between two dates

Re: Check if a Certain day of the week falls between two dates

Re: Check if a Certain day of the week falls between two dates

Re: Check if a Certain day of the week falls between two dates

Re: Check if a Certain day of the week falls between two dates

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions