Need help with a sum formula that omits cells

Hi,

I have a formula that adds up the total amount of hours worked for a scheduled work week that works but i cant seem to figure out how to omit cells from the formula that have text in them instead of a time. Since the schedule is always changing i would like to be able to write text into any of the boxes instead of a work time if need be without having to change the formula each time. As of right now when i enter in a word to one of the cells i get #VALUE!

Here is the formula i have so far =24*(SUM((C12-B12)+(E12-D12)+(G12-F12)+(I12-H12)+(K12-J12)+(M12-L12)+(O12-N12)))

So C12 is the out time on Monday and b12 is the in time then so on for Tuesday through Sunday.

I attached a photo if that helps.

Thank you!

Try this:

=24*(SUM(C12,E12,G12,I12,K12,M12,O12)-SUM(B12,D12,F12,H12,J12,L12,N12))

That didn't work. It left me with an answer of 12:30 AM

Thank you TMS!! this works perfectly

Ok so here is something i noticed.. haha. I realized that if the shift goes through midnight it ends up coming up as a negative number.

Is there a way to add in an additional formula to the statement to each subtraction piece that checks if the answer for those two cells is negative and if so it adds 24 to it?

Here is the Statement TMS came up with.

=24*((IFERROR((C17-B17),0)+IFERROR((E17-D17),0)+IFERROR((G17-F17),0)+IFERROR((I17-H17),0)+IFERROR((K17-J17),0)+IFERROR((M17-L17),0)+IFERROR((O17-N17),0)))

Change IFERROR((C17-B17),0) to =IFERROR(MOD(C17-B17,1),0)

Awesome!! Thank you Puocam. This works perfectly!