Why am I getting formula errors in Numbers after iOS 18.4 update?

Hi Mtbpolack,

The combination of IF OR is your friend.

If an employee does not sign either in or out, then DURATION inserts 0h

Formula in G2 is IF(OR(C2="",D2=""),DURATION(,,0),D2−C2)

Formula in H2 is IF(OR(E2="",F2=""),DURATION(,,0),F2−E2)

Fill down.

DURATION(,,0) means ignore weeks and days and insert 0 hours.

Please call back with questions.

Regards,

Ian.

Use conditional highlighting to hide 0h.

Duration equal to 0ms then white text to "hide" against the white cell fill.

Something I only just noticed: your screen shot has three rows per day. Do your employees clock in and out multiple times per day?

Regards,

Ian.

Mtbpolack wrote:

It is possible for our employees to work multiple jobs in a day, especially superintendents.

Ok, I understand.

See if this will work for you and an employee "Susan".

Formula in H2 is IF(OR(C2="",D2=""),DURATION(,,0),D2−G2−C2)

That allows for morning tea, lunch and afternoon tea breaks.

I strongly suggest that Columns C, D G be formatted as Text. That removes the Date that can cause problems with "crossing midnight" if the employee needs to edit a time on the next day.

Regards,

Ian.

The change probably was not the iOS version, Numbers was probably also updated.

I'm surprised previous versions allowed comparing a duration to a number. It allowed it but didn't give the "correct" result if the expectation was that 0 is the same as a duration of 0. If C7 though F7 are filled in with date&time values, F7-E7+D7-C7 will never equal the number 0 even if F7-E7+D7-C7 is a duration of 0 minutes. It equals 0 if all four cells are empty but not if any are filled in.

A formula similar to your original is:

=IF(C7&D7&E7&F7="","",DUR2HOURS(F7−E7+D7−C7))

One similar to your second formula is

=IF(C7&D7&E7&F7="","",F7−E7+D7−C7)

Format the column as duration with units hours and minutes

These will result in error triangles as you are typing in the times (just like the formulas you posted), unlike Yellowbox's formulas that wait until there is a pair of times before doing any calculating.

The built-in templates can be useful. If you haven't done so already you might have a look at the 'Signi In' table in the 'Employee Schedule' template.

SG