+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Dr. Darrell
    Guest

    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. #2
    Ian
    Guest

    Re: 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" <DrDarrell@discussions.microsoft.com> wrote in message
    news:787AB9A7-C6F2-4C54-B155-25B0703734DA@microsoft.com...
    >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. #3
    Dr. Darrell
    Guest

    Re: 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" <DrDarrell@discussions.microsoft.com> wrote in message
    > news:787AB9A7-C6F2-4C54-B155-25B0703734DA@microsoft.com...
    > >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. #4
    Ron Rosenfeld
    Guest

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

    On Wed, 7 Dec 2005 03:37:02 -0800, "Dr. Darrell"
    <DrDarrell@discussions.microsoft.com> 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. #5
    Roger Govier
    Guest

    Re: 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 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. #6
    Roger Govier
    Guest

    Re: 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"
    > <DrDarrell@discussions.microsoft.com> 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. #7
    Ron Rosenfeld
    Guest

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

    On Wed, 07 Dec 2005 14:57:04 +0000, Roger Govier
    <roger@nospamtechnology4u.co.uk> wrote:

    >Much nicer, Ron.
    >
    >Regards
    >
    >Roger Govier
    >


    Thanks
    --ron

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1