|
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 |
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 |
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 |
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 |
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 |
=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 |
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 |
:) 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 |
| All times are GMT +1. The time now is 12:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com