time constraints for SLA

**NickyC** · 10-16-2012, 08:19 AM

looks like it needs a tweak for periods less than a full day apart - try this instead:

=12*IF(NETWORKDAYS(A1,B1)>2,NETWORKDAYS(ROUNDUP(A1,0),ROUNDDOWN(B1,0)-1),0)+24*(IF(WEEKDAY(A1,2)<6,IF(MOD(A1,1)<0.25,0.5,IF(MOD(A1,1)<0.75,0.75-MOD(A1,1),0)),0)+IF(WEEKDAY(B1,2)<6,IF(MOD(B1,1)<0.25,0,IF(MOD(B1,1)<0.75,MOD(B1,1)-0.25,0.5)),0))

an example -

if start time is 4pm on Wed 18 july and end time is 11am on Mon 23 July then:

the first part returns 24 hours on the two whole working days between the start and end days (Thu and Fri)
the second returns 2 hours worked between 4pm and 6pm on the starting day, Wed
the third returns 5 hours between 6am and 11 am on the end day, Mon

for a total of 31 hours

**penfold1992** · 10-16-2012, 08:59 AM

this appears to be the work of genius, I am truely astonished by your excellence good sir!

but yes, it still falls short on values less then a day...

i have 10:50 to 13:30 and it kicks out a value of 14.666 hours when it should be 2.666 meaning it has added a day.

EDIT: also, it is indeed slowing excel down an aweful lot. admittingly the data im using is too large currently however its changed from a 2 minute operation to a 15minute operation and counting =)

EDIT:EDIT: the single days dont matter so much as ill be filtering them out anyway, would be nice to see it working perfect though... Another point to mention is that i am going to be using this in a macro and it churns out some bad results because it changes the code

=12*IF(NETWORKDAYS($B:$B,'D2')>2,NETWORKDAYS(ROUNDUP($B:$B,0),ROUNDDOWN('D2',0)-1),0)+24*(IF(WEEKDAY($B:$B,2)<6,IF(MOD($B:$B,1)<0.25,0.5,IF(MOD($B:$B,1)<0.75,0.75-MOD($B:$B,1),0)),0)+IF(WEEKDAY('D2',2)<6,IF(MOD('D2',1)<0.25,0,IF(MOD('D2',1)<0.75,MOD('D2',1)-0.25,0.5)),0))

is there anyway to prevent this?

ps:
the code i enter in is:

"=12*IF(NETWORKDAYS(C2,D2)>2,NETWORKDAYS(ROUNDUP(C2,0),ROUNDDOWN(D2,0)-1),0)+24*(IF(WEEKDAY(C2,2)<6,IF(MOD(C2,1)<0.25,0.5,IF(MOD(C2,1)<0.75,0.75-MOD(C2,1),0)),0)+IF(WEEKDAY(D2,2)<6,IF(MOD(D2,1)<0.25,0,IF(MOD(D2,1)<0.75,MOD(D2,1)-0.25,0.5)),0))"

and then i autofill it downwards.