I am trying to get the Difference between the a Submit Date time stamp and A Date Approved time stamp not counting non working hours ( before 9:00 AM, after 5:PM, wee kends and Holidays) in hours using excel.
I used this to bring the time portion into working hours range =IF(MOD(A2,1)<E2,INT(A2)+E2,IF(MOD(A2,1)>F2,INT(A2)+F2,A2))
And I use this to pull out the non working hours and week end/Holiday
=IF(AND(INT(B2)=INT(D2),NOT(ISNA(MATCH(INT(B2),$J$2:$J$9,0)))),0,ABS(IF(INT(B2)=INT(D2),ROUND(24*(D2-B2),2),(24*(F2-E2)*(MAX(NETWORKDAYS(B2+1,D2-1,$J$2:$J$9),0)+INT(24*(((D2-INT(D2))-(B2-INT(B2)))+(F2-E2))/(24*(F2-E2))))+MOD(ROUND(((24*(D2-INT(D2)))-24*E2)+(24*F2-(24*(B2-INT(B2)))),2),ROUND((24*(F2-E2)),2))))))
This set up can not handle if the time stamp falls on a week end/Holiday.
If any one has done something along this line or is very good in excel I would be more than happy to send a sample excel sheet to show them more details.
Thank you all
My Email is Jon_Rich@ML.com
I used this to bring the time portion into working hours range =IF(MOD(A2,1)<E2,INT(A2)+E2,IF(MOD(A2,1)>F2,INT(A2)+F2,A2))
And I use this to pull out the non working hours and week end/Holiday
=IF(AND(INT(B2)=INT(D2),NOT(ISNA(MATCH(INT(B2),$J$2:$J$9,0)))),0,ABS(IF(INT(B2)=INT(D2),ROUND(24*(D2-B2),2),(24*(F2-E2)*(MAX(NETWORKDAYS(B2+1,D2-1,$J$2:$J$9),0)+INT(24*(((D2-INT(D2))-(B2-INT(B2)))+(F2-E2))/(24*(F2-E2))))+MOD(ROUND(((24*(D2-INT(D2)))-24*E2)+(24*F2-(24*(B2-INT(B2)))),2),ROUND((24*(F2-E2)),2))))))
This set up can not handle if the time stamp falls on a week end/Holiday.
If any one has done something along this line or is very good in excel I would be more than happy to send a sample excel sheet to show them more details.
Thank you all
My Email is Jon_Rich@ML.com