hide zeros & negatives for date formulas
 

I am working a data sheet with start/finish dates from which the bosses want
charts made etc. If I don't have a finish date, I get a massive negative
number, which skews everything. Plus the formula I am using for working
hours leaves me with a "-8" in every form block that has not yet had data
entered. Is there a way to suppress these negative numbers without affecting
format?

hide zeros & negatives for date formulas
 

you could wrap your formulas like this to output 0 instead of negative numbers

=Max(formula,0)

or this will output a blank space (which is ignored by SUM and AVERAGE)

=IF(formula<0,"",formula)

"kimdnw" wrote:

I am working a data sheet with start/finish dates from which the bosses want
charts made etc. If I don't have a finish date, I get a massive negative
number, which skews everything. Plus the formula I am using for working
hours leaves me with a "-8" in every form block that has not yet had data
entered. Is there a way to suppress these negative numbers without affecting
format?

hide zeros & negatives for date formulas
 

OK, so my existing formula is:
=(NETWORKDAYS(Start,Finish, Holiday)-1)*8+(MOD(D3,1)-MOD(C3,1))*24
based on an 8 hour work day and a 24 hour clock

New formula is:
=MAX((NETWORKDAYS(C3,D3, A44:A60)-1)*8+(MOD(D3,1)-MOD(C3,1))*24,0)

Seems to work! Thanks Sloth!

Kimdnw

"Sloth" wrote:

you could wrap your formulas like this to output 0 instead of negative numbers

=Max(formula,0)

or this will output a blank space (which is ignored by SUM and AVERAGE)

=IF(formula<0,"",formula)