Turn Around Time with Set Break Times

[azhrei]

Hello,
I'm trying to get a formula that'll calculate the TAT, considering break times that may pause production. I found a great thread here from some years ago I Need a formula to calculate the Turn Around Time that answers 90% of my question. With the solution posted there,

Re: I Need a formual to calculate the Turn Around Time

If you have start time/date in A2 and end time/date in B2 you can use this formula in C2

=(NETWORKDAYS(A2,B2)-1)*("17:00"-"8:00")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:00","8:00"),"17:00")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:00","8:00")

format C2 as [h]:mm

How could I take this and implement the subtraction of scheduled breaks? I have a break from 10-10:10, lunch from 12:30-13:00, and another break from 15:00-15:10. So essentially if my start and end time run between any of those breaks, the formula will subtract that time from the result. Any help is appreciated, thank you!

 

[azhrei]

Sorry for my misstake, its exactly what below
=SUMPRODUCT((MOD("23:00"-"1:00",1)>MOD(A2:B2-"1:00",1))*MOD("23:00"-A2:B2,1)*{1,-1})+MOD("23:00"-"1:00",1)*((MOD(B2-A2,1)>MOD("1:00"-A2,1))+INT(B2-A2))

I also found the above solution in this thread Calculate time difference between two times with removing non working hours, however I'd need three non-working times removed, and I do need to account for holidays and weekends. I'm attempting to find the solution myself however am only coming across formulas that partially address my particular needs.

 

[azhrei]

Perhaps I should've titled this as the Cycle Time as opposed to Turn Around Time but I can't seem to edit the subject line. Still been searching for an answer but no luck so far. Thankful for any guidance available!

 

[azhrei]

So what I ended up doing was:

=(NETWORKDAYS(O3,P3,Misc.!$B$2:B)-1)*("16:20"-"8:05")+IF(NETWORKDAYS(P3,P3,Misc.!$B$2:B),MEDIAN(MOD(P3,1),"16:20","8:05"),"16:20")-MEDIAN(NETWORKDAYS(O3,O3,Misc.!$B$2:B)*MOD(O3,1),"16:20","8:05")-("00:10"*R3)-("00:30"*S3)

Where I have start time/date in O3, end time/date in P3, and formula in Q3. R3 is 10 minute breaks and S3 is 30 minute lunch (both counted as 1, 2, etc.). I'm manually entering how many breaks and lunches should be factored in the formula. While working, is this the best solution for this or is there a way to automate those breaks based on my start/end date and times?