Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old December 7th 05, 11:37 AM posted to microsoft.public.excel.worksheet.functions
Dr. Darrell
 
Posts: n/a
Default 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((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


  #2   Report Post  
Old December 7th 05, 11:52 AM posted to microsoft.public.excel.worksheet.functions
Ian
 
Posts: n/a
Default 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



  #3   Report Post  
Old December 7th 05, 12:35 PM posted to microsoft.public.excel.worksheet.functions
Dr. Darrell
 
Posts: n/a
Default 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




  #4   Report Post  
Old December 7th 05, 01:00 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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 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


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
  #5   Report Post  
Old December 7th 05, 01:36 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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(C1414)=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 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



  #6   Report Post  
Old December 7th 05, 02:57 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier
 
Posts: n/a
Default 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 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



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

  #7   Report Post  
Old December 7th 05, 08:21 PM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Negative Number column go New Users to Excel 1 November 28th 05 01:10 AM
Find a negative number in a range Scott at Culvers Excel Worksheet Functions 1 November 22nd 05 04:55 PM
Change Number to a negative in VBA Noemi Excel Discussion (Misc queries) 2 November 22nd 05 11:58 AM
Show last number entered hodkd Excel Discussion (Misc queries) 2 November 9th 05 08:31 PM
how do i subtract a negative number anndee Excel Worksheet Functions 2 June 11th 05 08:14 PM


All times are GMT +1. The time now is 02:49 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017