TAT minutes including weekend

[jodipy]

Hi all,

This is my first post. I've search and read some post but couldn't get what I was looking for.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Received time
[/TD]
[TD]Complete time
[/TD]
[TD]TAT (mins)
[/TD]
[TD]Business hours (include weekends/public holidays)
[/TD]
[/TR]
[TR]
[TD]21/11/2015 11:00PM
[/TD]
[TD]22/11/2015 10:00AM
[/TD]
[TD]60
[/TD]
[TD]9:00AM - 9:00PM
[/TD]
[/TR]
[TR]
[TD]22/11/2015 11:00AM
[/TD]
[TD]22/11/2015 11:45AM
[/TD]
[TD]45
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Above is an example of my setup. I've tried to use networkdays to calculate but the result is wrong.
Anyone can help?

 

[DRSteele]

What you're after is an algorithm that calculates working hours over dates, like a NETWORKHOURS function similar to NETWORKDAYS. Excel does not have such a function, but I wish it did. Please go to this website, register and vote for it: Create NETWORKHOURS as a new function

In the meantime, review this thread, which itself refers to other threads, and see if it is relevant. http://www.mrexcel.com/forum/excel-...-calculation-complex-formula.html#post4291215

Try this:

	A	B	C	D
Start Time	End Time
Received Time	Completed Time	hours	minutes

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #F4B084, align: right"]9:00:00 AM[/TD]
[TD="bgcolor: #F4B084, align: right"]9:00:00 PM[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFF2CC"]TAT[/TD]
[TD="bgcolor: #FFF2CC"]TAT[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]Oct 07, 2015 9:00 AM[/TD]
[TD="align: right"]Oct 11, 2015 5:00 PM[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 56.00 [/TD]
[TD="bgcolor: #E2EFDA, align: right"] 3,360.00 [/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]Oct 17, 2015 10:00 AM[/TD]
[TD="align: right"]Oct 17, 2015 11:05 PM[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 11.00 [/TD]
[TD="bgcolor: #E2EFDA, align: right"] 660.00 [/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]Oct 19, 2015 7:30 AM[/TD]
[TD="align: right"]Oct 19, 2015 12:05 PM[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 3.08 [/TD]
[TD="bgcolor: #E2EFDA, align: right"] 185.00 [/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]Nov 21, 2015 11:00 AM[/TD]
[TD="align: right"]Nov 22, 2015 10:00 AM[/TD]
[TD="bgcolor: #E2EFDA, align: right"] 11.00 [/TD]
[TD="bgcolor: #E2EFDA, align: right"] 660.00 [/TD]

</tbody>

Sheet12​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: #DAE7F5"]C6[/TH]
[TD="align: left"]=IF(OR($D$2<$C$2,B6<A6),0,
(DAYS(B6,A6)+1
-(IF(MOD(A6,1)>$D$2,1,
(MAX($C$2,MOD(A6,1))-$C$2)
/($D$2-$C$2)))
-(IF(MOD(B6,1)<$C$2,1,
($D$2-MIN($D$2,MOD(B6,1)))
/($D$2-$C$2))))
*($D$2-$C$2)*24)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D6[/TH]
[TD="align: left"]=C6*60[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[jodipy]

Thank you s os much for response.
I've tried your method and copy out to excel. My result is 0.00.
Is there any add-in to install?

[TABLE="width: 341"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9:00:00 AM[/TD]
[TD]9:00:00 PM[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]TAT[/TD]
[TD]TAT[/TD]
[/TR]
[TR]
[TD]Received Time[/TD]
[TD]Completed Time[/TD]
[TD]hours[/TD]
[TD]minutes[/TD]
[/TR]
[TR]
[TD]07/10/15 9:00[/TD]
[TD]11/10/15 17:00[/TD]
[TD]0.00[/TD]
[TD]0.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[DRSteele]

There is no add-in; it's a simple formula.

You do need to put the dates in as dates and not as text; you can format them however you like, but they must be entered as MS Excel dates.

 

[jodipy]

There is no add-in; it's a simple formula.

You do need to put the dates in as dates and not as text; you can format them however you like, but they must be entered as MS Excel dates.

I've formatted with custom "mmm dd, yyyy hh:mm AM/PM" format. Is this correct?

 

[jodipy]

I've formatted with custom "mmm dd, yyyy hh:mm AM/PM" format. Is this correct?

DRSteele: Can you share me your template? I can't seem to get it right.

 

[DRSteele]

Hi. I will try to help you more sometime early this week. I'm not near a desktop.

I can't imagine what the problem might be here. Try to copy everything into the proper cells and make sure dates are Excel Dates.

Hang tough.

 

[jodipy]

Tried to copy to different excel to execute. Result shows 0.00 as well.
Thanks for helping.

 

[DRSteele]

This is the formula that should go in C6 and be copied down. Are you copying-and-pasting correctly?

=IF(OR($D$2<$C$2,B6<a6),0,(days(b6,a6)+1-(if(mod(a6,1)>$D$2,1,(MAX($C$2,MOD(A6,1))-$C$2)/($D$2-$C$2)))-(IF(MOD(B6,1)<$C$2,1,($D$2-MIN($D$2,MOD(B6,1)))/($D$2-$C$2))))*($D$2-$C$2)*24)

</a6),0,(days(b6,a6)+1-(if(mod(a6,1)>

 

[jodipy]

This is the formula that should go in C6 and be copied down. Are you copying-and-pasting correctly?

=IF(OR($D$2<$C$2,B6<a6),0,(days(b6,a6)+1-(if(mod(a6,1)>$D$2,1,(MAX($C$2,MOD(A6,1))-$C$2)/($D$2-$C$2)))-(IF(MOD(B6,1)<$C$2,1,($D$2-MIN($D$2,MOD(B6,1)))/($D$2-$C$2))))*($D$2-$C$2)*24)

</a6),0,(days(b6,a6)+1-(if(mod(a6,1)>

There is error in above formula
The original formula and this formula is different right?