+ Reply to Thread
Results 1 to 8 of 8

Help - Need experts help!

  1. #1
    Registered User
    Join Date
    05-31-2005
    Posts
    13

    Unhappy Help - Need experts help!

    I am having a problem with adding a column of time values and need an experts help!

    Column b has the time in and is formated as Time 13:00.
    Column c has the time out and is formated as Time 13:00.
    Column d adds the total time which has a formula of -
    =if(c10>b10,c10-b10,1-(b10-c10))

    Then I have Column d adding up to give me a total of time. It is formated as Number. The formula is g=sum(g8:g26)*24

    This all works great as long as there is actual time entered. However when the cells in e & f as blank it still enters a total as it is taking it as 0:00 which it is counting it as 24:00 and adding this to the total. The formulas in b & c need to stay as is as this is the only way it will calculate ok if the b column is greater and the c column (ex: 18:00 as b and 6:00 as c)

    Can anyone tell me how to get around this problem? I would like to display the total as 0 if there are no times entered or blank. My spread sheet has 20 rows in which I can enter information if needed otherwise they sit blank....

    Thanks

  2. #2
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    If you follow the logic of your formula, two blank cells 'equal' each other. Your formula says that if c10 is NOT greater than b10 (which being equal matches) then subtract the difference (in this case, zero) from 1, which leaves 1, which Excel interprets as 24:00 in time format.

    Therefore, you need to exclude the situation where both values are equal, such as:

    =if(c10=b10,"",if(c10>b10,c10-b10,1-(b10-c10))) or
    =if(c10=b10,0,if(c10>b10,c10-b10,1-(b10-c10)))

    Does this work for you?

    Bruce
    Last edited by swatsp0p; 06-21-2005 at 09:05 AM.
    Bruce
    The older I get, the better I used to be.
    USA

  3. #3
    PegL
    Guest

    RE: Help - Need experts help!

    Or =if(c10>=b10,c10-b10,1-(b10-c10))

    "rvnwdr" wrote:

    >
    > I am having a problem with adding a column of time values and need an
    > experts help!
    >
    > Column b has the time in and is formated as Time 13:00.
    > Column c has the time out and is formated as Time 13:00.
    > Column d adds the total time which has a formula of -
    > =if(c10>b10,c10-b10,1-(b10-c10))
    >
    > Then I have Column d adding up to give me a total of time. It is
    > formated as Number. The formula is g=sum(g8:g26)*24
    >
    > This all works great as long as there is actual time entered. However
    > when the cells in e & f as blank it still enters a total as it is
    > taking it as 0:00 which it is counting it as 24:00 and adding this to
    > the total. The formulas in b & c need to stay as is as this is the only
    > way it will calculate ok if the b column is greater and the c column
    > (ex: 18:00 as b and 6:00 as c)
    >
    > Can anyone tell me how to get around this problem? I would like to
    > display the total as 0 if there are no times entered or blank. My
    > spread sheet has 20 rows in which I can enter information if needed
    > otherwise they sit blank....
    >
    > Thanks
    >
    >
    > --
    > rvnwdr
    > ------------------------------------------------------------------------
    > rvnwdr's Profile: http://www.excelforum.com/member.php...o&userid=23903
    > View this thread: http://www.excelforum.com/showthread...hreadid=380839
    >
    >


  4. #4
    RagDyeR
    Guest

    Re: Help - Need experts help!

    One way would be to calculate your daily hours in *every* row, then just
    some the daily hours column with a regular SUM() function:

    =((C10-B10+(C10<B10))*24)*AND(B10<>0,C10<>0)

    --

    HTH,

    RD
    ==============================================
    Please keep all correspondence within the Group, so all may benefit!
    ==============================================


    "rvnwdr" <[email protected]> wrote in
    message news:[email protected]...

    I am having a problem with adding a column of time values and need an
    experts help!

    Column b has the time in and is formated as Time 13:00.
    Column c has the time out and is formated as Time 13:00.
    Column d adds the total time which has a formula of -
    =if(c10>b10,c10-b10,1-(b10-c10))

    Then I have Column d adding up to give me a total of time. It is
    formated as Number. The formula is g=sum(g8:g26)*24

    This all works great as long as there is actual time entered. However
    when the cells in e & f as blank it still enters a total as it is
    taking it as 0:00 which it is counting it as 24:00 and adding this to
    the total. The formulas in b & c need to stay as is as this is the only
    way it will calculate ok if the b column is greater and the c column
    (ex: 18:00 as b and 6:00 as c)

    Can anyone tell me how to get around this problem? I would like to
    display the total as 0 if there are no times entered or blank. My
    spread sheet has 20 rows in which I can enter information if needed
    otherwise they sit blank....

    Thanks


    --
    rvnwdr
    ------------------------------------------------------------------------
    rvnwdr's Profile:
    http://www.excelforum.com/member.php...o&userid=23903
    View this thread: http://www.excelforum.com/showthread...hreadid=380839



  5. #5
    Registered User
    Join Date
    05-31-2005
    Posts
    13
    Thanks Bruce, that worked quite nice.

    To take this one step farther - what would I have to do if I would want the system to automatically change the value in column D to 3 hrs if the total of D were to calculate to below this value?

    Thanks

  6. #6
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066
    =IF(IF(C10=B10,0,IF(C10>B10,C10-B10,1-(B10-C10)))<3/24,3/24,IF(C10=B10,0,IF(C10>B10,C10-B10,1-(B10-C10))))

    Mangesh

  7. #7
    Forum Expert swatsp0p's Avatar
    Join Date
    10-07-2004
    Location
    Kentucky, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,545
    Thanks, Mangesh... that is perfect!

    Cheers!!

    Bruce

  8. #8
    Forum Contributor
    Join Date
    06-10-2004
    Location
    India
    Posts
    1,066


    Mangesh

+ 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