several dates ("holidays'" in a column) between 2 dates

[web3master]

Hello group!

please see attached file time_tracking.xlsx (saved/created on Excel for MAC 2011)

Up to this point I was able to figure pretty much everything out on my own, some 'formulas' might not be that great and few things might not be perfect, but it works for me and does the job...

Here's my problem where I'll need your help:

Please see column T, the holidays of 2014
For each of the lines 6 to 11 (Customer 1 to Customer 6, many more will follow) I do need a formula that figures out if ANY of the dates in column T are between the dates in C6 and F6
IF that's the case, I'd need a number 1 to show in L6, if that's not the case, I'd need a number 0 to show in L6

For example, none of the holidays in column T are within the dates of lines 6 to 10, in those lines I'd need the number 0 in L6 to L10
BUT
the 2nd holiday in column T (4/18/2014) falls between C11 and F11... Here I would now need the number 1 in L11


That's basically it...

If you're interested what the whole thing is for:
the large green box gives me the average of the days in column M
the large orange box gives me the average in hours (decimal and then in hrs and mins)

days are easily calculated using the dates
but what i can not calculate/use are weekends, "off"-hours, holidays, etc...
only working-hours from 8am to 5pm with 1 hour lunch are calculated (at least i hope so, haha)

columns H to L

H
figures out if there's a weekend between the dates in column C and column F

I
gives me a 1 if IN-time is before 12 noon

J
gives me a 1 if OUT-time is after noon

K
gives me a 1 if weekend between dates in column C and F and if the OUT-time is after noon

L
I'll need your help with this one as I tried to explain above
L should give me the number 1 if ANY of the dates in column T fall between the IN-date (column C) and the OUT-date (column F)


THANK YOU ALL SO MUCH FOR YOUR HELP!!!!
Martin

[Bernie Deitrick]

For your problem, in L6 use the formula

=IF(SUMPRODUCT((C6<=$T$2:$T$20)*(F6>=$T$2:$T$20))>0,1,0)

where T2:T20 have the holiday dates. (I did not open your file, so adjust the range as needed.)

I also assumed C6 is earlier than F6 - they may need to be switched if that is not the case.

[web3master]

=IF(SUMPRODUCT((C6<=$T$2:$T$20)*(F6>=$T$2:$T$20))>0,1,0)

Mr. Deitrick, THANK YOU
that's amazing...
do you "just know" those things?
wow

thank you again!!

[Bernie Deitrick]

do you "just know" those things?

I started using Lotus 123 in 1985, so I've had a long time to learn spreadsheet and data logic.

[Bernie Deitrick]

do you "just know" those things?

I started using Lotus 123 in 1985, so I've had a long time to learn spreadsheet and data logic.