Articles

Solved tat time difference in hours

January 16, 2013 at 03:37:40
Specs: Windows XP


Hi

I'm trying to calculate the TAT in hours (72 Hours) considering the time difference between out date and time to in date and time. Also excluding non-working days (Sat, Sund and Holidays).

Additionally some items may have multiple lines with different dates and times, can you please assist with a formula such that when i pivot the date it should be able to calculate the summed up time difference hours for the item.


In date In Time Out Date Out Time
12-DEC-2012 13:35:28 19-DEC-2012 12:37:50


Thank you


See More: tat time difference in hours

Report •


#1
January 16, 2013 at 23:06:43
✔ Best Answer

Hi

I would use the NETWORKDAYS function for this. This function will not include weekends and if you give a list of holiday dates it will also exclude those. the format is

NETWORKDAYS(Start date, End date, Range of holiday dates)

For your example I'll assume the day starts at 9:00AM and finishes at 5:00PM
So the equation will be

=NETWORKDAYS(A1,C1,K1:K20)-(B1-(9/24))-((17/24)-D1)

where
A1 contains the the start date
B1 the start time
C1 the end date
D1 the end time
K1:K20 contains your list of holiday dates

the result for your example is 5.63 days

Wed 12-Dec-12	13:35:28	Wed 19-Dec-12	12:37:50	5.63

or 45.01 hours (using an 8 hour day)

=(NETWORKDAYS(A1,C1,K1:K20)-(B1-(9/24))-((17/24)-D1))*8

Wed 12-Dec-12	13:35:28	Wed 19-Dec-12	12:37:50	45.01


Report •
Related Solutions


Ask Question