phillipcook
Board Regular
- Joined
- Jun 25, 2015
- Messages
- 87
Hey guys I have the following formula:
=IF(G2=“”,””,IF(NETWORKDAYS(B2,B2,$S$2:$S$22)>0,MIN(18/24,H2)-(MAX(6/24,G2),0))
B column = date
S2:S22 = holidays
H column = finish time
G column = start time
what the formula is meant to do is if the date is a Monday - Friday and not a public holiday, calculate the total time worked between 6am and 6pm. The formula dose this, however I’m having issues with the formula when the start and finish times are both outside of the 6am-6pm area. The fact emulate cell is just displaying #########.
What i I need to do is have the formula display the cell as blank when there is no start time or the date is a weekend or holiday or if the day actually is actually a weekday but all of the hours worked are outside the 6am-6pm.
Thanks in advance.
=IF(G2=“”,””,IF(NETWORKDAYS(B2,B2,$S$2:$S$22)>0,MIN(18/24,H2)-(MAX(6/24,G2),0))
B column = date
S2:S22 = holidays
H column = finish time
G column = start time
what the formula is meant to do is if the date is a Monday - Friday and not a public holiday, calculate the total time worked between 6am and 6pm. The formula dose this, however I’m having issues with the formula when the start and finish times are both outside of the 6am-6pm area. The fact emulate cell is just displaying #########.
What i I need to do is have the formula display the cell as blank when there is no start time or the date is a weekend or holiday or if the day actually is actually a weekday but all of the hours worked are outside the 6am-6pm.
Thanks in advance.
Last edited:
