Adding an error check to ISNUMBER formula

[lukela85]

Hi all,

The formulas below calculate if any additional hours are worked by a staff member on a particular day.

=ROUND(IF(ISNUMBER(G39),H39-G39-'Hours Matrix'!D26-IF(AND(G39<0.500694444,H39>0.60416667),1/24,0),0)*96,0)/96

=ROUND(IF(ISNUMBER(G19),H19-G19-'Hours Matrix'!D6-IF(AND(GI9<0.500694444,H19>0.60416667),1/24,0),0),15)

I have listed both formulas above as though they both currently do what I ask I'm not sure if it would be easier to achieve what I would like with one over the other.

Currently when a shift is entered in H-G cells which is shorter than the normal shift length for a staff member on a particular day (Hours Matrix D6/D26) the value returned is negative so ### is displayed. I would like the formula to return an error message stating the problem rather than just returning ###.

Hope this makes sense,

Thanks,

Luke

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Oops, are you really using 2003?

[lukela85]

I bought Excel 2013 earlier today however the formula above is still returning a negative value if the shift is shorter than the length listed in Hours Matrix?

[TMS]

You asked for an error check which is what you have. It would seem that you need to check your data. We can't do that for you, we don't have it.

Regards, TMS

[ChemistB]

The IFERROR function doesn't catch the formatting error Excel has when working with negative time. You'll need something like

Please Login or Register  to view this content.

You might be able to shorten that somewhat but hard to say without seeing data.

[lukela85]

Excel is now saying the logical test is invalid.

[ChemistB]

Sorry, try this

Please Login or Register  to view this content.