Hi Darrell

One way would be to multiply by a test fro the count in the matching pairs
of cells to be equal to 2. This will return True or False which can be
coerced to 1 or 0 by preceding with the double unary minus.

=(D14-C14)*24*--(COUNT(C14:D14)=2)+(F14-E14)*24*--(COUNT(E14:F14)=2)

Regards

Roger Govier


Dr. Darrell wrote:
> I have created a virtual timecard. Everything works good except, if I haven’t
> filled in all my time in and time out cells, my result is a negative number.
> I would prefer that the result is blank unless there is a whole number.
>
> My structure is as follows:
>
> Cell C14 Clock In Format h:mm
> Cell D14 Clock Out Format h:mm
> Cell E14 Clock In Format h:mm
> Cell F14 Clock Out Format h:mm
> Cell J14 Total Hours Formula =SUM((D14-C14)*24,(F14-E14)*24)
>
> The result of my efforts are as follows:
>
> When – Scenario # 1
>
> Cell C14 Clock In 6:45
> Cell D14 Clock Out Blank
> Cell E14 Clock In Blank
> Cell F14 Clock Out Blank
> Cell J14 Total Hours -6.75
>
> When– Scenario # 2
>
> Cell C14 Clock In 6:45
> Cell D14 Clock Out 12:00
> Cell E14 Clock In Blank
> Cell F14 Clock Out Blank
> Cell J14 Total Hours 5.25
>
> When– Scenario # 3
>
> Cell C14 Clock In 6:45
> Cell D14 Clock Out 12:00
> Cell E14 Clock In 12:30
> Cell F14 Clock Out Blank
> Cell J14 Total Hours -7.25
>
> When– Scenario # 4
>
> Cell C14 Clock In 6:45
> Cell D14 Clock Out 12:00
> Cell E14 Clock In 12:30
> Cell F14 Clock Out 16:00
> Cell J14 Total Hours 8.75
>
> I don't want the negative numbers to show in the Total Hours Cell, instead I
> would like:
>
> In Scenario # 1, I would like Cell J14 to be Blank
> In Scenario # 3, I would like Cell J14 to be equal to that of Scenario # 2.
>
> I have tried several variations of IF statements, and I’ve yet to type in
> the correct syntax.
>
> Can someone direct me down the right path?
>
> Respectfully,
> Darrell
>