Ignore text in formula

[sw101]

Need to sum and multiply in a time sheet style formula and ignore text

=SUM((K26-H26)+(I26-J26))*24

Columns are Time In, Time Out, Break in, Break Out

I'd like to put either a - or n/a if they do not take a break and then sum their hours.

 

[Eric W]

Welcome to the Board.

Try:

=((K26-H26)+(N(I26)-N(J26)))*24

or

=((K26-H26)+IFERROR(I26-J26,0))*24


Note that the SUM is not needed in this case.

 

[sw101]

I get a #VALUE ! error message.

 

[Eric W]

Which formula did you use, and what values do you have in K26, H26, I26 and J26?

 

[sw101]

I tried both. No error message initially (when I left the break in/break out cells blank) but when I put any text into it, it then returned that error.

K26 -
H26 13:00
I26 17:00
J26 -

 

[Eric W]

Sorry, I misunderstood which fields belonged to Time In/Out Break In/Out. Try:

=(I26-H26-IFERROR(K26-J26,0))*24

 

[sandy666]

maybe: =SUM(K26,-N(H26),SUM(I26,-N(J26))*24)

adapt to your needs (I don't know where could be "-" , randomly? )