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
Bookmarks