# Help - Need experts help!

1. ## 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. 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

3. ## 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
>
>

4. ## 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" <rvnwdr.1qz9ei_1119359169.3007@excelforum-nospam.com> wrote in
message news:rvnwdr.1qz9ei_1119359169.3007@excelforum-nospam.com...

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

5. 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. =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. Thanks, Mangesh... that is perfect!

Cheers!!

Bruce

8. Mangesh

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

#### 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