Help with a formula.

[phillipcook]

Hi Guys

I have the following formula:

=IF(G2="","",IF(NETWORKDAYS(B2,B2,$AH$2:$AH$22)>0,MIN(18/24,H2)-MAX(6/24,G2),0))

References:
B2 - Date
G2 - Start Time
H2 - Finish Time
AH2:AH22 - Public Holiday Dates

What this formula is meant to do is the following:
1- Look at the date to determine if is a Monday to Friday and not a public holiday.
2- If false due to the date being weekend or a public holiday then the cell shows a blank.
3- If it is true then it looks at the start and finish times and calculates how many hours were worked between 06:00 and 18:00.
3- if the the answer is 00:00 (zero time) then the cell will show blank (this is the part that doesn't seem to work correctly)

The formula seems to work as long as at least the start of finish time has some part of it within 06:00 till 18:00 window (for example 05:00 - 10:00 would work with a result of 04:00 or 14:00 - 20:00 with also a result of 04:00 and 05:00 - 19:00 would return a result of 12:00), however if both the start and finish times are before 06:00 or both after 18:00 (for example 03:00-05:00 or 20:00 - 22:00) then the formula returns a heap of ######### instead of a blank cell

Can anyone please help me figure out how to make the formula show a blank cell if both the start and finish are before 06:00 or both after 18:00?

Thanks in advance guy.

 

[AlanY]

try replace G2="", with

=or(G2="",AND(G2 < TIME(6,0,0),H2 < TIME(6,0,0)),AND(G2 > TIME(18,0,0),H2 > TIME(18,0,0)))

 

[phillipcook]

Is this what you mean?

=IF(=or(G2="",AND(G2 < TIME(6,0,0),H2 < TIME(6,0,0)),AND(G2 > TIME(18,0,0),H2 > TIME(18,0,0))"",IF(NETWORKDAYS(B2,B2,$AH$2:$AH$22)>0,MIN(18/24,H2)-MAX(6/24,G2),0))

 

[AlanY]

Is this what you mean?

=IF(=or(G2="",AND(G2 < TIME(6,0,0),H2 < TIME(6,0,0)),AND(G2 > TIME(18,0,0),H2 > TIME(18,0,0))"",IF(NETWORKDAYS(B2,B2,$AH$2:$AH$22)>0,MIN(18/24,H2)-MAX(6/24,G2),0))

close, give it a try

=IF(or(G2="",AND(G2 < TIME(6,0,0),H2 < TIME(6,0,0)),AND(G2 > TIME(18,0,0),H2 > TIME(18,0,0))),"",IF(NETWORKDAYS(B2,B2,$AH$2:$AH$22)>0,MIN(18/24,H2)-MAX(6/24,G2),0))

 

[phillipcook]

Your a life saver!! been trying to figure out this one for a while!!!