Home 
Search 
Today's Posts 
#1




Instead of a negative number, I'd like to show zero...
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((D14C14)*24,(F14E14)*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 
#2




Instead of a negative number, I'd like to show zero...
=IF(D14=0,0,IF(F14=0,(D14C14)*24,SUM((D14C14)*24,(F14E14)*24)))
If D14=0 then 0, else if F14=0 then D14C14 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((D14C14)*24,(F14E14)*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 
#3




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,(D14C14)*24,SUM((D14C14)*24,(F14E14)*24))) If D14=0 then 0, else if F14=0 then D14C14 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((D14C14)*24,(F14E14)*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 
#4




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((D14C14)*24,(F14E14)*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,D14C14)+MAX(0,F14E14))*24 Format as: Format/Cells/Number/Custom Type: #,##0.00_);(#,##0.00); or something equivalent so the zero's don't show. ron 
#5




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. =(D14C14)*24*(COUNT(C1414)=2)+(F14E14)*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((D14C14)*24,(F14E14)*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 
#6




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((D14C14)*24,(F14E14)*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,D14C14)+MAX(0,F14E14))*24 Format as: Format/Cells/Number/Custom Type: #,##0.00_);(#,##0.00); or something equivalent so the zero's don't show. ron 
#7




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Negative Number column  New Users to Excel  
Find a negative number in a range  Excel Worksheet Functions  
Change Number to a negative in VBA  Excel Discussion (Misc queries)  
Show last number entered  Excel Discussion (Misc queries)  
how do i subtract a negative number  Excel Worksheet Functions 