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.

Received time	Complete time	TAT (mins)	Business hours (include weekends/public holidays)
21/11/2015 11:00PM	22/11/2015 10:00AM	60	9:00AM - 9:00PM
22/11/2015 11:00AM	22/11/2015 11:45AM	45

<tbody>
</tbody>

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
1			Start Time	End Time
2			9:00:00 AM	9:00:00 PM
3
4			TAT	TAT
5	Received Time	Completed Time	hours	minutes
6	Oct 07, 2015 9:00 AM	Oct 11, 2015 5:00 PM	56.00	3,360.00
7	Oct 17, 2015 10:00 AM	Oct 17, 2015 11:05 PM	11.00	660.00
8	Oct 19, 2015 7:30 AM	Oct 19, 2015 12:05 PM	3.08	185.00
9	Nov 21, 2015 11:00 AM	Nov 22, 2015 10:00 AM	11.00	660.00

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet12​

Worksheet Formulas Cell Formula C6 =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) D6 =C6*60 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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?

		Start Time	End Time
		9:00:00 AM	9:00:00 PM
		TAT	TAT
Received Time	Completed Time	hours	minutes
07/10/15 9:00	11/10/15 17:00	0.00	0.00

<colgroup><col span="2"><col><col></colgroup><tbody>
</tbody>

 

[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?