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

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

"MBC" <(E-Mail Removed)> wrote in message
news:4ECBC06F-4129-4143-B758-(E-Mail Removed)...
>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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"MBC" <(E-Mail Removed)> wrote in message
news:4ECBC06F-4129-4143-B758-(E-Mail Removed)...
>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

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

"Bernard Liengme" wrote:

> 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
> --
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
>
> "MBC" <(E-Mail Removed)> wrote in message
> news:4ECBC06F-4129-4143-B758-(E-Mail Removed)...
> >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
>
>
>