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

I have created a virtual timecard. Everything works good except, if I havent
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 Ive yet to type in
the correct syntax.

Can someone direct me down the right path?

Respectfully,
Darrell

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

=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.
--
Ian
--
"Dr. Darrell" wrote in message
...
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

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

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

Darrell

"Ian" wrote:

=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.
--
Ian
--
"Dr. Darrell" wrote in message
...
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

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

On Wed, 7 Dec 2005 03:37:02 -0800, "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

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

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

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 havent
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 Ive yet to type in
the correct syntax.

Can someone direct me down the right path?

Respectfully,
Darrell

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

Much nicer, Ron.

Regards

Roger Govier


Ron Rosenfeld wrote:
On Wed, 7 Dec 2005 03:37:02 -0800, "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


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

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

On Wed, 07 Dec 2005 14:57:04 +0000, Roger Govier
wrote:

Much nicer, Ron.

Regards

Roger Govier


Thanks
--ron