Instead of a negative number, I'd like to show zero...

[Guest]

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

 

[Ian]

=IF(D14=0,0,IF(F14=0,(D14-C14)*24,SUM((D14-C14)*24,(F14-E14)*24)))
If D14=0 then 0, else if F14=0 then D14-C14 else your original formula.

 

[Guest]

Thank you Ian. Now that I see it in writing, it makes great sence.

Darrell

 

[Ron Rosenfeld]

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

No need for an IF statement:

=(MAX(0,D14-C14)+MAX(0,F14-E14))*24

Format as: Format/Cells/Number/Custom Type:

#,##0.00_);(#,##0.00);

or something equivalent so the zero's don't show.


--ron

 

[Roger Govier]

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(C1414)=2)+(F14-E14)*24*--(COUNT(E14:F14)=2)

Regards

Roger Govier

 

[Roger Govier]

Much nicer, Ron.

Regards

Roger Govier

No need for an IF statement:

=(MAX(0,D14-C14)+MAX(0,F14-E14))*24

Format as: Format/Cells/Number/Custom Type:

#,##0.00_);(#,##0.00);

or something equivalent so the zero's don't show.


--ron

 

[Ron Rosenfeld]

Much nicer, Ron.

Regards

Roger Govier

Thanks
--ron