Excel Help with Dates

[quakeworld]

Hi, I'm trying to find out if there's a certain day between 2 dates. Cell A1 has 5/11/12 and A2 has 5/15/12. I'm stuck coming up with the formula to find out if there's a Friday between those two dates. Any help is appreciated.

 

[NeoV]

Is there any limit as to how big the gap could be between A1 and A2?

There is a command called "weekday(cell) that will return a numerical value for each day of the week - in this case you are looking for a 6.

You could nestle that into some if statements to look for that formula returning a 6...something like

=IF(A2-1>A1,IF(WEEKDAY(A2-1)=6,"Yes",IF(A2-2>A1,IF(WEEKDAY(A2-2)=6,"Yes",IF(A2-3>A1,IF(WEEKDAY(A2-3)=6,"Yes","No"),"No")))))

That would check if there is a 3-day gap - you'd have to continued the pattern X number of times to account for how large your gaps might be

 

[PowerEngineer]

Try this: IF(MOD(WEEKDAY(MIN(A1,A2))+1,7)+ABS(A1-A2)>7,"YES","NO")

The sevens are for the number of days in the week.

The one is seven minus the day of the week you are looking for, so seven minus six in this case.

The order of the dates doesn't matter.

The Friday must be between the two dates. Fridays for the two dates given do not count.

 

[NeoV]

what does that "mod" function do?

 

[corkyg]

5/11/12 is a Friday. 5/15/12 is a Tuesday. A simple glance at a calendar says there is no Friday between the two dates - just Sat, Sun and Mon. Why make things so complicated? Just click on the date in the Notification area and it pops up. Sounds like busy work to me. 🙂

 

[NeoV]

uh....corky - usually in a spreadsheet you are dealing with multiple instances of this - just taking a guess

 

[corkyg]

uh....corky - usually in a spreadsheet you are dealing with multiple instances of this - just taking a guess

Yes, . . . and all of those instances access the same calendar anytime. Maybe that's part of a solution, a macro that links the calendar. 🙂

 

[ringtail]

Better resource for your FUTURE Excel needs

 

[quakeworld]

Try this: IF(MOD(WEEKDAY(MIN(A1,A2))+1,7)+ABS(A1-A2)>7,"YES","NO")

The sevens are for the number of days in the week.

The one is seven minus the day of the week you are looking for, so seven minus six in this case.

The order of the dates doesn't matter.

The Friday must be between the two dates. Fridays for the two dates given do not count.

This works. Thanks P.E.!