I am having a problem with adding a column of time values.

Column e has the time called out and is formated as Time 13:00.
Column f has the time in and is formated as Time 13:00.
Column g adds the total time which has a formula of -
=if(f10>e10,f10-e10,1-(e10-f10))

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

This all works great as long as there is actual time entered.  Howeve
when the cells in e & f as blank it still enters a total as it i
taking it as 0:00 which it is counting it as 24:00.  

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

Thank

--
rvnwd
-----------------------------------------------------------------------
rvnwdr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2390
View this thread: http://www.excelforum.com/showthread.php?threadid=37743

You are wrong, it is your formula in G that creates this error, if the time 
fields are empty your formula returns 1 which is the same as 24 hours, change 
the formula in G to (I am using your example in G10)

=MOD(E10-F10,1)

or if I misunderstood you

=MOD(End_time-Start_time)

that is regardless if the end time technically is less than the start time, 
another way which maybe makes more sense


=E10-F10+(E10<F10)

having said that I find it odd that you use E as end time and F as start time
I would say 99% of excel users would put IN in E and END in F

regardless, if I misunderstood just reverse the cells but these formulas 
will work

Regards,

Peo Sjoblom


"rvnwdr" wrote:

> 
> I am having a problem with adding a column of time values.
> 
> Column e has the time called out and is formated as Time 13:00.
> Column f has the time in and is formated as Time 13:00.
> Column g adds the total time which has a formula of -
> =if(f10>e10,f10-e10,1-(e10-f10))
> 
> Then I have Column g 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.  
> 
> 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.  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?action=getinfo&userid=23903
> View this thread: http://www.excelforum.com/showthread.php?threadid=377433
> 
> 

One way, if I understand you correctly:

G10:    =IF(COUNT(E10:F10)<2,0,MOD(F10-E10,1))
Gtot:   =IF(COUNT(G8:G26)>0,SUM(G8:G26)*24,"")

Format G8:G26 as time.
Format Gtot with Format/Cells/Number/Custom [h]:mm




In article <rvnwdr.1qbhub_1118250304.4176@excelforum-nospam.com>,
 rvnwdr <rvnwdr.1qbhub_1118250304.4176@excelforum-nospam.com> wrote:

> I am having a problem with adding a column of time values.
> 
> Column e has the time called out and is formated as Time 13:00.
> Column f has the time in and is formated as Time 13:00.
> Column g adds the total time which has a formula of -
> =if(f10>e10,f10-e10,1-(e10-f10))
> 
> Then I have Column g 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.  
> 
> 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.  My spread sheet
> has 20 rows in which I can enter information if needed otherwise they
> sit blank....
> 
> Thanks