Time Calculation

sacastiglia

New Member
Joined
Jul 29, 2014
Messages
23
Hello All,

Anyone who can help with this will be a hero in my book. I am using the following formula to calculate drive time hours (hours prior to being on site and hours after leaving site) and it works as long as there are hours listed for drive start and drive end. Some of our technicians do not have drive time (i.e. submitting the timesheet for vacation, sick time, etc. or if there was no drive time for the day)

=SUM((J7-I7)*24)+SUM(N7-M7)*24

The formula I am using to calculate total site hours works fine because it only calculates the actual site hours and does not take into account drive time hours.

The problem I am having is if there is no time entered for Drive Start and Drive end, I am getting number for the total drive hours that is the negative twin of the total site hours.

I don't want to have to add any columns as there would be a great amount of code that would need to be changed.

Thank you
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I can't tell from just that formula where the problem is, but maybe

=IF(COUNT(I7, J7)=2, (J7-I7)*24) + IF(COUNT(N7, M7)=2, (N7-M7)*24)
 
Upvote 0
This formula did work if there were no drive hours, but rendered a 0 calculation if there were drive time hours.
 
Upvote 0
Not for me:

I​
J​
K​
L​
M​
N​
O​
P​
Q​
R​
6​
Start​
End​
Start​
End​
Hours​
7​
08:30​
09:15​
14:20​
15:43​
2.1​
Q7: =IF(COUNT(I7, J7)=2, (J7-I7)*24) + IF(COUNT(N7, M7)=2, (N7-M7)*24)
8​
Format of Q7: 0.0
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top