Subtraction formula with Text error

R

RustywitExl

This is a timesheet:

A1 B1 C1 D1 E1 F1
Time 9:00 am 12:00 pm 9:00 am 12:00 pm OFF
Total 3 3 #Value!

I have time in and time out with total hours caculating. The formula I am
using for total hours is a simple C1-B1 with HH:MM format.

I need to enter OFF, SICK, OUT for days that the employee is not in but I
get an error value. How can I make it so that the formula adds the hours
entered and ignores any other values such as OFF, SICK, OUT.
 
T

T. Valko

The formula I am using for total hours is a simple C1-B1

Try this...

=IF(COUNT(B1:C1)=2,C1-B1,"")

And use the SUM function to get a grand total:

=SUM(B2:F2)
 
M

Ms-Exl-Learner

Try this...

=IF(OR(C1={"OFF","SICK","OUT"},B1={"OFF","SICK","OUT"}),"",C1-B1)

Remember to Click Yes, if this post helps!
 
R

RustywitExl

thanks, i tried but get a FALSE statement. The problem I see myself running
into is that there are so many possibilities for an employee been out aside
from OFF, Sick, etc. It would be hard to always have to add the reason to the
formula. Is there an easier way to just have the formula ignore anything that
is text?
 
T

T. Valko

Is there an easier way to just have the
formula ignore anything that is text?

Yes. Use the COUNT function like I did to make sure there are 2 time values
entered.
=IF(COUNT(B1:C1)=2,C1-B1,"")
i tried but get a FALSE statement.

Hmmm...

That formula will *never* return FALSE. It will either subtract B1 from C1
*only* if there are 2 time values (numbers) in the respective cells or it
will return a blank.
 
D

David Biddulph

The formula which Biff gave you *cannot* return FALSE, and it *does* ignore
anything that is text. Perhaps you need to read it again?
 
R

RustywitExl

Thank you for helping.

Ms-Exl-Learner said:
Try this...

=IF(OR(C1={"OFF","SICK","OUT"},B1={"OFF","SICK","OUT"}),"",C1-B1)

Remember to Click Yes, if this post helps!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top