How to make a cell which has a conditional format sum with text in return as a 0

[Richard Dkn]

I have created a schedule which incorporates lunches automatically (either half hour or 1-hour lunches, depending on the circumstances).

The schedule only has time in and time out, excel automatically deducts the lunch break depending on the circumstance:

If you worked less than 6 hours = no lunch
If you worked more than 6 hours, but less than or equal to 8 hours = 0.5 hour lunch
If you worked more than 8 hours = 1 hour lunch

The formula (which works) I’m using for this is:

9:00 AM in A2 and 5:00 PM in B2 or 09:00 and 17:00

=(B2-A2)*24-IF((B2-A2)*24>8,1,IF((B2-A2)*24>6,0.5,0))

But…

If I put any text in a cell, such as “HOL” or “OFF” to indicate Holiday or Day off the cell returns #VALUE – how do I make the cell return “0” if TEXT is applied to either A2 or A2 cells?

Please help – I’m loosing the will to live!

Many thanks

[Jonmo1]

Try

=IF(COUNT(A2:B2)=2,(B2-A2)*24-IF((B2-A2)*24>8,1,IF((B2-A2)*24>6,0.5,0)),0)

[Richard Dkn]

Awesome - thanks so much!!

Just one more, for night shifts (where no break is deducted) i have the following formula

=(MOD(B1-C1,1))*24, again how do I return it as a zero value if text is applied to cells B or C.

[Jonmo1]

Pretty much the same way..

=IF(COUNT(B1:C1)=2,MOD(B1-C1,1)*24,0)