Calculate Time Difference (Business Hours Only)

[Mike H..]

Is no one able to give an opinion???

No help out there??

Ian M

Hi,

This formula is now horrendous and I would normally give up and resort to a VBA solution but let's see if this solves the issues. The new formula is in Col H and I've re-directed the formula in Col G to reference this new column.

=IF((NETWORKDAYS(A2,B2)-1)*("17:30"-"08:00")+MEDIAN(MOD(B2,1),"08:00","17:30")-MEDIAN(MOD(A2,1),"08:00","17:30")>0,(NETWORKDAYS(A2,B2)-1)*("17:30"-"08:00")+MEDIAN(MOD(B2,1),"08:00","17:30")-MEDIAN(MOD(A2,1),"08:00","17:30"),(NETWORKDAYS(IF(WEEKDAY(A2,2)>=6,INT(A2)+(7-WEEKDAY(A2,2)+1)+TIME(0,0,1),A2),B2)-1)*("17:30"-"08:00")+MEDIAN(MOD(B2,1),"08:00","17:30")-MEDIAN(IF(WEEKDAY(A2,2)<6,MOD(A2,1),TIME(0,0,1)),"08:00","17:30"))

 

[ianamck]

Hi Mike

Sorry to hear about your loss happy to wait for a response. I think I understand why some of the answers are coming out wrong. Some of the tickets are being closed before they are supposed to be able to so they are ending up as less than zero hours.

Hear from you when you can mate.

Best Wishes
Ian M

 

[Mike H..]

Hi Mike

Sorry to hear about your loss happy to wait for a response. I think I understand why some of the answers are coming out wrong. Some of the tickets are being closed before they are supposed to be able to so they are ending up as less than zero hours.

Hear from you when you can mate.

Best Wishes
Ian M

Ian,

Thanks for your kind words. I posted a response.

 

[ianamck]

Mike it appears to be solving the issues I found will let you know over the next couple of days.

Thanks for all you help. I have learnt new things and as always frustrated myself by knowing I should be better at this.

Much appreciated
Best Wishes
Ian M