Help - Need experts help!

R

rvnwdr

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
 
S

swatsp0p

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
 
R

RagDyeR

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!
==============================================


message
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
 
R

rvnwdr

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
 
M

mangesh_yadav

=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))))

Manges
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top