Time Calculation

[sacastiglia]

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

 

[shg]

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]

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

 

[shg]

Not for me:

[Table="width:, class:grid"][tr][td] [/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][td]

P​

[/td][td]

Q​

[/td][td]

R​

[/td][/tr]
[tr][td]

6​

[/td][td]

Start​

[/td][td]

End​

[/td][td][/td][td][/td][td]

Start​

[/td][td]

End​

[/td][td][/td][td][/td][td]

Hours​

[/td][td][/td][/tr]

[tr][td]

7​

[/td][td]

08:30​

[/td][td]

09:15​

[/td][td][/td][td][/td][td]

14:20​

[/td][td]

15:43​

[/td][td][/td][td][/td][td]

2.1​

[/td][td]Q7: =IF(COUNT(I7, J7)=2, (J7-I7)*24) + IF(COUNT(N7, M7)=2, (N7-M7)*24)[/td][/tr]

[tr][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Format of Q7: 0.0[/td][/tr]
[/table]