# Needing assistance with calculating TAT in Hours

#### jsheets

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

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!

### 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
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

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

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

@jsheets, you are welcome

#### Dave Patton

##### Well-known Member

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

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

##### New Member

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:

#### Dave Patton

##### Well-known Member
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)

Replies
7
Views
99
Replies
10
Views
358
Replies
19
Views
309
Replies
1
Views
167
Replies
18
Views
375