# Time Calculation

#### sacastiglia

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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### shg

##### MrExcel MVP
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)

#### sacastiglia

##### New Member
This formula did work if there were no drive hours, but rendered a 0 calculation if there were drive time hours.

#### shg

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

Replies
1
Views
213
Replies
2
Views
452
Replies
1
Views
336
Replies
4
Views
209
Replies
1
Views
107

1,195,671
Messages
6,011,077
Members
441,581
Latest member
rp4717

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

### Which adblocker are you using?

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

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