Needing assistance with calculating TAT in Hours

jsheets

New Member
Joined
Oct 28, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
135
Office Version
  1. 2013
Platform
  1. Windows
Assuming the two dates are in Cells A1 and B1. In Cell C1:

Code:
=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
 
Last edited:

cortexnotion

Board Regular
Joined
Jan 22, 2020
Messages
135
Office Version
  1. 2013
Platform
  1. Windows
Slight adjustment...

Code:
=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

New Member
Joined
Oct 28, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Slight adjustment...

Code:
=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

Board Regular
Joined
Jan 22, 2020
Messages
135
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

@jsheets, you are welcome :)
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,499
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
For your consideration

Date and Time.xlsm
ABC
1Oct 22, 2020 15:07Oct 27, 2020 12:3169.4
2
3Rate10.00
4$694.00
15c
Cell Formulas
RangeFormula
C1C1=(NETWORKDAYS(A1,B1)-2)*24+MOD(B1-A1,1)*24
C4C4=C1*C3
 

jsheets

New Member
Joined
Oct 28, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.

1603971205885.png


Example 1:
1603971190972.png


Example 2:
1603971246188.png
 

jsheets

New Member
Joined
Oct 28, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
For your consideration

Date and Time.xlsm
ABC
1Oct 22, 2020 15:07Oct 27, 2020 12:3169.4
2
3Rate10.00
4$694.00
15c
Cell Formulas
RangeFormula
C1C1=(NETWORKDAYS(A1,B1)-2)*24+MOD(B1-A1,1)*24
C4C4=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:
1603971469040.png
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,499
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Date and Time.xlsm
ABCD
2Oct 22, 2020 16:34Oct 22, 2020 15:32-1.03Received an hour before it is sent?
3Oct 22, 2020 15:07Oct 27, 2020 12:3169.40
15c
Cell Formulas
RangeFormula
C2:C3C2=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)
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,118,773
Messages
5,574,155
Members
412,574
Latest member
shadowfighter666
Top