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