Needing assistance with calculating TAT in Hours

[jsheets]

Hello, I am trying to find a formula to calculate TAT in Hours

Received Time: 10/22/2020 15:07
Replied Time: 10/27/2020 12:31
And to only calculate Mon-Fri since it we are closed on Sat/Sun

Thinking the end result should read 69:24:00 or 69 hours 24 minutes.

Any help is much appreciated!

 

[cortexnotion]

Assuming the two dates are in Cells A1 and B1. In Cell C1:

=SUM((24*(NETWORKDAYS(A1,B1)-SIGN(NETWORKDAYS(A1,B1))))+TEXT(B1-A1,"hh"))&" Hours "&RIGHT(TEXT(B1-A1,"hh:mm"),2)&" Minutes"

I have not added the option for holiday dates

 

[cortexnotion]

Slight adjustment...

=SUM((24*((NETWORKDAYS(A1,B1)-SIGN(NETWORKDAYS(A1,B1))-1)))+TEXT(B1-A1,"hh"))&" Hours "&RIGHT(TEXT(B1-A1,"hh:mm"),2)&" Minutes"code]

 

[jsheets]

Slight adjustment...

=SUM((24*((NETWORKDAYS(A1,B1)-SIGN(NETWORKDAYS(A1,B1))-1)))+TEXT(B1-A1,"hh"))&" Hours "&RIGHT(TEXT(B1-A1,"hh:mm"),2)&" Minutes"code]

This worked great! Thank you so much!

 

[cortexnotion]

@jsheets, you are welcome

 

[Dave Patton]

For your consideration

Date and Time.xlsm
	A	B	C
1	Oct 22, 2020 15:07	Oct 27, 2020 12:31	69.4
2
3	Rate		10.00
4			$694.00
15c

Cell Formulas
Range	Formula
C1	C1	=(NETWORKDAYS(A1,B1)-2)*24+MOD(B1-A1,1)*24
C4	C4	=C1*C3

 

[jsheets]

This worked great! Thank you so much!

After further review it worked to an extent. So it did return the value correctly on the first one which was in example 1. I did notice that not all were correct as in example 2, this I should read 24 hours 1 minute or 24:01:00.

Example 1:

Example 2:

 

[jsheets]

For your consideration

Date and Time.xlsm
	A	B	C
1	Oct 22, 2020 15:07	Oct 27, 2020 12:31	69.4
2
3	Rate		10.00
4			$694.00
15c

Cell Formulas
Range	Formula
C1	C1	=(NETWORKDAYS(A1,B1)-2)*24+MOD(B1-A1,1)*24
C4	C4	=C1*C3

I tried this formula as well however it isn't populating the correct TAT either. It is giving me a negative number which should read 1 hour and 2 min or 01:02:00.

Example:

 

[Dave Patton]

Date and Time.xlsm
	A	B	C	D
2	Oct 22, 2020 16:34	Oct 22, 2020 15:32	-1.03	Received an hour before it is sent?
3	Oct 22, 2020 15:07	Oct 27, 2020 12:31	69.40
15c

Cell Formulas
Range	Formula
C2:C3	C2	=IF(INT(A2)=INT(B2),IF(A2>B2,-MOD(A2-B2,1)*24,MOD(B2-A2,1)*24),(NETWORKDAYS(A2,B2)-2)*24+MOD(B2-A2,1)*24)