You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

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
Question marked as Top-ranking reply

Posted on Nov 4, 2023 4:49 PM

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))

Similar questions

6 replies
Question marked as Top-ranking reply

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))

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

Zero as a duration

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.