How do I calculate SUM when one of the values is "0"

Question

Level 1

0 points

How do I calculate SUM when one of the values is "0"

I am creating a time sheet for a type of employee who will clock in and out a few times a day. Each time they clock in and out it will calculate the total time worked for that time period. They can have up to 4 of these per work day.

I have a cell to calculate all of those totals, to show the TOTAL time worked that day. If there isnt't a calculation in EACH block, then the total time worked errors.

HELP. How do I fix this?

EX:

Monday

In 6am

Out 7am - Total 1hr

In 9am

Out 11am - Total 2 hr

In 2pm

Out 3pm - Total 1 hr

In 4pm

Out 5pm - Total 1hr

Total Time worked 5hr

If the employee was done for the day after the 2-3pm shift, therefore nothing is entered into the 4th 'shift' area, then the total time wouldn't calculate. PLEASE help me!

MacBook

Posted on Dec 7, 2013 4:32 PM

Reply

Answer 1

SGIII

Level 8

35,324 points

Dec 7, 2013 5:31 PM in response to pinkwaves013

Hi pinkwaves013,

This is one way to handle this:

The formula in D2, copied down is:

=IF(LEN(B2)×LEN(C)=0,"",C2−B2)

This puts null (nothing) in the cell if either the In or the Out time on that row is blank or zero, so you don't get the error that prevents the total from calculating.

The formula in D6 (row 6 is a Footer Row) is =SUM(D).

The cells in the body of columns B and C are formatted as Date & Time with 'None' chosen for the Date.

SG

Reply

Answer 2

pinkwaves013 Author

Level 1

0 points

Dec 7, 2013 7:42 PM in response to SGIII

I am not familiar at all with the terms and formulas really, so although I think you are giving me the answer I want, I don't understand it or how to apply it into my ss.

Is there a simpler way to state the formula for --if nothing is entered in the time 'in' or 'out' the cell will say 0, therefore the other cell calculating total time worked will calculate/add correctly?

I don't know what LEN or the "" means. Sorry, appreciate the help!

Reply

Answer 3

Badunit

Level 7

26,020 points

Dec 7, 2013 8:19 PM in response to pinkwaves013

If you want a zero when "in" or "out" is empty, you have two choices:

Format the cell as Date and Time then use this formula

=IF(LEN(B2)*LEN(C2)=0,0,C2−B2)

Or, let the format be automatic and use this formula but the the result must be 0h, not a plain old zero:

=IF(LEN(B2)*LEN(C2)=0,DURATION(,,0),C2−B2)

The LEN function gives you the number of characters in a string. That is, the length of the string. If B2 is empty, the length of the string in that cell is zero. Same thing with C2. If either or both cells are empty, LEN(B2) * LEN(C2) = 0

I see we all must be careful about Numbers '09 versus Numbers 3 when posting formulas here. The multiplication symbol of "x" works fine in Numbers 3 but not in Numbers '09. Use a * if using Numbers '09.

With the vast differences between the versions of Numbers, it would be very helpful if you mentioned which version you are using when posting a question. Or put it in your profile so it shows up at the bottom of your posts.

Reply

Answer 4

SGIII

Level 8

35,324 points

Dec 7, 2013 8:20 PM in response to pinkwaves013

The basic structure of an if statement is like this: =IF(this is true, do this, else do that).

LEN(B2) means get me the "length" of the contents in B2. If there's nothing in B2 (no In time has been entered there) its result is 0.

LEN(C2) does the same for C2.

If either B2 or C2 is blank, then LEN(B2) x LEN(C2) is 0. (That's the "this is true" in the formula.)

If LEN(B2) x LEN(C2) is 0, then the formula says put "" or null/nothing in the cell. (That's the "do this" in the formula).

Otherwise, it calculates C2-B2 (Out time minus In time) and puts that in the cell. (That's the "do that" in the formula).

SG

Reply