Adding times

[MBC]

I am using the following formula to add times (24 hour) in MS Excel 2003
=IF(J<I,J+1-I,J-I) . I is my start time J is my finish time. The formula
works ok when a start and finish time are entered but if ONLY a start time or
finish time are entered the "total hours" cell is populated with incorrect
data. Is there any way I can say"if either I or J are empty then "total Hours
= 0

 

[Pete_UK]

Try this:

=IF(OR(I="",J=""),0,IF(J<I,J+1-I,J-I))

Obviously, I and J need to be cell references (unless they are named
ranges).

Hope this helps.

Pete

 

[Bernard Liengme]

Let's begin by simplifying your formula to
=(J1-I1)+(J1>I1)
which is functional identical to your formula. The second term evaluates to
0 or 1 but when Excel does arithmetic on Boolean values it takes FALSE as 0
and TRUE as 1

Now we can test for cells with no data (blank cells are considered to equal
zero):
=IF(OR(I1=0,J1=0),0,(J1-I1)+(J1>I1))

best wishes

 

[MBC]

Thanks Bernard, that solved my initial problem. However I am trying to work
out the "shift Gap"(Rest time) between two shifts which are on different
worksheets in the same file. Worksheet 17 cell ref AB3 contains my finish
time of 17:00 and worksheet 18 cell ref I10 contains my shift start time of
8:00. The answer is 15 but the formula I am using gives me an answer of 17?.
Cant see where I am going wrong.
=IF('Week 17'!AB3=0,24,IF('Week 17'!AB3>I3,('Week
17'!AB3-I3)*24,24-(I3-'Week 17'!AB3)*24))