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.

User profile for user: tyc2010
tyc2010 Author
User level: Level 1
8 points

Calculate 0h durations in a timesheet?

My job requires us to turn in timesheets for each pay period and I am trying to turn it digital to make it a little easier. In the process, I have gotten Numbers to calculate the durations I need unless there is no time present. If I don't have a clock in/out present, the calculation is 0 rather than 0h and it throws everything off. I tried changing the cell format to duration but when I click off the cell and go back to it, it changes back to automatic. I tried doing an if statement in the function but if I try to type any duration into the function (i.e. 0h) it simply won't let me. Is there a way to get the calculation to default to 0h if there isn't a time present to calculate?

Posted on Apr 25, 2023 8:28 AM

Reply
Question marked as Top-ranking reply
User profile for user: SGIII
SGIII
User level: Level 8
36,134 points

Posted on Apr 25, 2023 9:03 AM

There are various ways to deal with this problem but I like the one Apple provides in the 'Employee Schedule' template at File > New in your menu:




=IF(OR(ISBLANK(C2),ISBLANK(D2)),DURATION(0),D2−C2)



This inserts a 0h Duration if either C or D are blank; otherwise it subtracts to Date & Times for a result in Duration.


Use ; instead of , in the formula if your regions uses , as the decimal separator.



SG

View in context

Similar questions

2 replies
Question marked as Top-ranking reply
User profile for user: SGIII
SGIII
User level: Level 8
36,134 points

Apr 25, 2023 9:03 AM in response to tyc2010

There are various ways to deal with this problem but I like the one Apple provides in the 'Employee Schedule' template at File > New in your menu:




=IF(OR(ISBLANK(C2),ISBLANK(D2)),DURATION(0),D2−C2)



This inserts a 0h Duration if either C or D are blank; otherwise it subtracts to Date & Times for a result in Duration.


Use ; instead of , in the formula if your regions uses , as the decimal separator.



SG

Reply

Calculate 0h durations in a timesheet?

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