Time Log daily hours error when no time entered

I made a Time Log in Numbers. I have multiple clock in and clock out time slots per day. The formula runs correctly when there is time in the slots. When time is not in the slots there is an error that occurs in the total at the bottom of my spreadsheet. I just want to display it as blank if there is not time in the slots so its ready to calculate whenever I add time into that new day's slot.


Below you can see 4 of the [ Hours ]'s cells have the same formula and are working. (Row 7 is empty)

BUT

When the formula shows 0.0, the total, at the end of the sheet, is broken. The simple formula that is in the [ TOTAL Hrs ] cell is SUM(G3:G28). This is all the [ Hours ] totaled up together.


It doesnt like that 0.0. When the formula in the 0.0 cell is removed, it is happy:


My sad attempt at a fix:

IF I don't put the calculation in the empty slots' [ Hours ] cell, then my time total at the end is happy. But WHAT A HASSLE to have to drag the code down into the next days' cell to calculate and add the time to the total hours cell at the bottom of my sheet...


What formula can I use in ALL the [ Hours ] cells - one that I can drag down into all the rows awaiting time to be entered?


This one I tried but even if there is time in the slots, it says I worked 0 hours. This is the right kind of concept I'm looking for - but it needs the "else" statement I dont understand how to write in.


My DREAM code would say: If the time slots are empty, dont show anything in that [ Hours ]'s cell. BUT if there is a time in there, display the total hours and mins logged. This code would not break the simple [ TOTAL Hrs ] cell at the bottom.


Can you help me? Clearly this isnt my forte.

Thanks!

MacBook Air 15″, macOS 15.1

Posted on Dec 10, 2024 10:00 AM

Reply
Question marked as Top-ranking reply

Posted on Dec 10, 2024 11:18 PM

Hi Lisa,


The error in the SUM is because it can not add Durations (hours and minutes, such as 2h 15m) and numbers (such as 0.00)



We need an IF formula to check for blank cells in C,D, E, F and insert a Duration.

Furthermore, I respectfully suggest calculating each shift separately, then adding (column I)

Try this:


Formula in E3 is IF(OR(C3="",D3=""),DURATION(0,0),D3−C3)

Formula in H3 is IF(OR(F3="",G3=""),DURATION(0,0),G3−F3)


The DURATION function can be a bit iffy. Please reply if you meet problems.

Regards,

Ian.

Similar questions

1 reply
Question marked as Top-ranking reply

Dec 10, 2024 11:18 PM in response to Lisa-Spinne

Hi Lisa,


The error in the SUM is because it can not add Durations (hours and minutes, such as 2h 15m) and numbers (such as 0.00)



We need an IF formula to check for blank cells in C,D, E, F and insert a Duration.

Furthermore, I respectfully suggest calculating each shift separately, then adding (column I)

Try this:


Formula in E3 is IF(OR(C3="",D3=""),DURATION(0,0),D3−C3)

Formula in H3 is IF(OR(F3="",G3=""),DURATION(0,0),G3−F3)


The DURATION function can be a bit iffy. Please reply if you meet problems.

Regards,

Ian.

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Time Log daily hours error when no time entered

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