Calculating Net Working Hours.

OK, this formula is a little complex....

Assuming the ticket start time/date is in A2 and in C2 you have the current time [ use =NOW()] then use this formula in D2 for time passed

="Age of ticket = "&TEXT((NETWORKDAYS(A2,C2)-1)/3+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1)-MEDIAN(0,1/24,MOD(C2,1)-7/12),K$3-1/24,K$2),K$3-1/24)-MEDIAN(NETWORKDAYS(A2,A2)*(MOD(A2,1)-MEDIAN(0,1/24,MOD(A2,1)-7/12)),K$3-1/24,K$2),"[h]:mm")

K2 should contain the weekday start time, i.e. 08:00 and K3 should contain the end time, i.e. 17:00

Then for time remaining use this formula

=IF(MID(D2,17,5)+0>="40:00"+0,"Late","Time Remaining = "&TEXT("40:00"-MID(D2,17,5),"[h]:mm"))

Note: NETWORKDAYS is part of Analysis ToolPak add-in

I prefer to keep any communication within the forum. If you have any further queries on this subject then just post another reply to this thread....or you can PM me if I don't respond