Zero as a duration

Question

Level 1

13 points

Zero as a duration

Hello

I have been struggling to find and answer to this -

I have a schedule that I would like to add a column of durations, the durations are derived from another predefined column using a tick box i.e. when the tick box is checked it shows the employee worked a particular set of hours.

When I tick the box E2 it populates a hidden column D.

E13 adds up hidden column D. But if Jane doesn't work a particular session, the tick box returns a negative value (0) and Numbers strips it of its durational format.

Only when all of the SUM values are returned (all the tick boxes are ticked), then the formatting total works.

Is there a way to force a duration on a "0" value so it is read as 0h 0m? I have tried cell formatting, but it always reverts back to a number.

Note - The blue rows are breaks and not included in the SUM.

Many Thanks

John

Posted on Nov 4, 2023 11:54 AM

Reply

Answer 1

Top-ranking reply

Recycleur

Level 4

1,609 points

Nov 4, 2023 4:49 PM in response to John Wilkie

You don't even need the hidden columns if you use SUMIFS (or SUMIF but I prefer SUMIFS as it's more versatile).

Give your table one header row and one footer row and you're good to go.

Enter this in D13 and copy to E13, F13, etc.

=SUMIFS($Dur.,WORK,TRUE)

(To enter $Dur and WORK just click on the column's letter, add the $ to Dur. after by checking "Preserve column").

I see you have the total time as decimal, so perhaps you could use this instead for Jane

=DUR2HOURS(SUMIFS($Dur.,Jane,TRUE))

Reply

Answer 2

Yellowbox

Level 7

21,197 points

Nov 4, 2023 6:39 PM in response to John Wilkie

Hi John,

I do not find the total hours to be 8h 0m. My table shows 9h 0m

I can not see any difference between my table and your table in Columns A and B.

Just as a test, I used another table to calculate Whole day Duration (18:30 minus 9:30)

Perhaps I have missed something.

Regards,

Ian.

Reply

Answer 3

Yellowbox

Level 7

21,197 points

Nov 4, 2023 3:52 PM in response to John Wilkie

Hi John,

The DURATION function is your friend.

Formula in D2: IF(E2,$C2,DURATION(,,0,0))

Fill down, then copy and paste into Column F

Regards,

Ian.

Reply

Answer 4

John Wilkie Author

Level 1

13 points

Nov 5, 2023 1:27 AM in response to Recycleur

Hi

Thanks for your help. This does look like a great solution, however, because of the layout of the worksheet (I only posted a small portion of the total sheet) creating a header and footer in the right places would have been difficult and I would have had to re-design the whole document. I will definitely use your suggestion in other future projects though.

Many Thanks

John

Reply

Answer 5

John Wilkie Author

Level 1

13 points

Nov 5, 2023 1:24 AM in response to Yellowbox

Hi Yellowbox

12:30 to 1:30 is a break, which the employees don't get paid for so the SUM equation in C deducts that from the total. Apologies, I should have shown that formula as well.

Thank you

Reply

Answer 6

John Wilkie Author

Level 1

13 points

Nov 5, 2023 1:28 AM in response to Yellowbox

This works perfectly. Thank you so much, it was driving me a bit crazy trying to find the solution.

Many Thanks

John

Reply

Zero as a duration

Similar questions