| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtraction formula with Text error
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtraction formula with Text error
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) -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtraction formula with Text error
Try this...
=IF(OR(C1={"OFF","SICK","OUT"},B1={"OFF","SICK","O UT"}),"",C1-B1) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "RustywitExl" wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtraction formula with Text error
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. Valko" wrote: 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) -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtraction formula with Text error
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. -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. Valko" wrote: 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) -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtraction formula with Text error
The formula which Biff gave you *cannot* return FALSE, and it *does* ignore
anything that is text. Perhaps you need to read it again? -- David Biddulph "RustywitExl" wrote in message ... 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. Valko" wrote: 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) -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtraction formula with Text error
Thank you so much, this is what i needed. You guys are the greatest!
"T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. Valko" wrote: 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) -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. . . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtraction formula with Text error
Thank you for helping.
"Ms-Exl-Learner" wrote: Try this... =IF(OR(C1={"OFF","SICK","OUT"},B1={"OFF","SICK","O UT"}),"",C1-B1) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "RustywitExl" wrote: 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. |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtraction formula with Text error
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... Thank you so much, this is what i needed. You guys are the greatest! "T. Valko" wrote: 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. -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. Valko" wrote: 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) -- Biff Microsoft Excel MVP "RustywitExl" wrote in message ... 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. . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subtraction that doesn't produce an error | Excel Discussion (Misc queries) | |||
| Keeping excel from performing subtraction so text remains as is | Excel Discussion (Misc queries) | |||
| What is the roundoff error in the subtraction function? | Excel Worksheet Functions | |||
| Error in Addition and Subtraction | Excel Discussion (Misc queries) | |||
| how do i do a subtraction formula | Excel Worksheet Functions | |||
Linear Mode
