Difference between the a Submit Date time stamp and A Date A

Jon Rich

New Member
Joined
Nov 14, 2005
Messages
1
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Watch MrExcel Video

Forum statistics

Threads
1,118,535
Messages
5,572,754
Members
412,482
Latest member
arooshrana2
Top