Avoiding Value error message due to space in cell w/ formula

[A.R. Hunt]

I have a time sheet in Excel that will be used by nearly 1000 employees. I'm
trying to make it both user friendly and foolproof. Currently, I have cells
for time in and out and then a cell that calculates the time worked. If
someone clears the "In" or "Out" cell with the space bar, the formula cell
returns a Value error message. How can I avoid this? Here's my formula:
=ROUND((SUM((((C16-B16)+(B16>C16))*1440)/60)+(((E16-D16)+(D16>E16))*1440/60))/0.25,0)*0.25

Thanks for any help correcting this or if you have suggestions regarding the
time formula I am very grateful.
A.R. Hunt

 

[Harlan Grove]

I have a time sheet in Excel that will be used by nearly 1000 employees. I'm
trying to make it both user friendly and foolproof. Currently, I have cells
for time in and out and then a cell that calculates the time worked. If
someone clears the "In" or "Out" cell with the space bar, the formula cell
returns a Value error message. How can I avoid this? Here's my formula:
=ROUND((SUM((((C16-B16)+(B16>C16))*1440)/60)
+(((E16-D16)+(D16>E16))*1440/60))/0.25,0)*0.25

....

So you're rounding to quarter hours? Easier to multiply by 96 (24*4)
than by 1440*60/0.25, 96 being no more obscure than 1440. Eliminate the
unnecessary SUM call and parentheses. Try

=ROUND((N(C16)-N(B16)+(B16>C16)+N(E16)-N(D16)+(D16>E16))*96,0)/4

N(.) returns 0 if its argument evaluates to text, FALSE or refers to a
blank cell. It'll only return nonzero numbers when its argument
evaluates to a number or TRUE, in which case it returns 1.

 

[pinmaster]

Hi,

One way:

=IF(ISERROR(ROUND((SUM((((C16-B16)+(B16>C16))*1440)/60)+(((E16-D16)+(D16>E16))*1440/60))/0.25,0)*0.2
),"",ROUND((SUM((((C16-B16)+(B16>C16))*1440)/60)+(((E16-D16)+(D16>E16))*1440/60))/0.25,0)*0.25
)

HTH
Jean-Guy

 

[A.R. Hunt]

Thanks! This seems to have fixed my problem. Thanks also for helping with the
time formula. I'd modified a formula from another thread here and was afraid
to make changes. Since I wasn't sure I understood all the arguments, I
figured I better use it pretty much as it was.