Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rvnwdr
 
Posts: n/a
Default 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(c10b10,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

  #2   Report Post  
swatsp0p
 
Posts: n/a
Default


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(c10b10,c10-b10,1-(b10-c10))) or
=if(c10=b10,0,if(c10b10,c10-b10,1-(b10-c10)))

Does this work for you?

Bruce


--
swatsp0p


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

  #3   Report Post  
PegL
 
Posts: n/a
Default

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(c10b10,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   Report Post  
RagDyeR
 
Posts: n/a
Default

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" wrote in
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(c10b10,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   Report Post  
rvnwdr
 
Posts: n/a
Default


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


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



  #6   Report Post  
mangesh_yadav
 
Posts: n/a
Default


=IF(IF(C10=B10,0,IF(C10B10,C10-B10,1-(B10-C10)))<3/24,3/24,IF(C10=B10,0,IF(C10B10,C10-B10,1-(B10-C10))))

Mangesh


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

  #7   Report Post  
swatsp0p
 
Posts: n/a
Default


Thanks, Mangesh... that is perfect!

Cheers!!

Bruce


--
swatsp0p


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

  #8   Report Post  
mangesh_yadav
 
Posts: n/a
Default


:)

Mangesh


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

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
for all of you excel experts BLW Excel Discussion (Misc queries) 1 May 17th 05 10:58 PM
another for the experts Wazooli Excel Discussion (Misc queries) 3 March 26th 05 07:21 AM
Question for the real experts! diepenbos Excel Discussion (Misc queries) 2 March 16th 05 03:21 PM
Help from Dave Peterson - no disrespect to all other experts Ann Excel Discussion (Misc queries) 1 March 10th 05 05:34 PM
Help from Dave Peterson - no disrespect to all other experts Ann Excel Discussion (Misc queries) 0 March 10th 05 12:45 PM


All times are GMT +1. The time now is 10:07 PM.

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

About Us

"It's about Microsoft Excel"