Apple Intelligence now features Image Playground, Genmoji, Writing Tools enhancements, seamless support for ChatGPT, and visual intelligence.

Apple Intelligence has also begun language expansion with localized English support for Australia, Canada, Ireland, New Zealand, South Africa, and the U.K. Learn more >

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.

Auto Calculating Timesheet using Numbers App

I’m looking for a Numbers app template that will allow entry of time arrival and departure including lunch. For example: Arrive at start of day, start lunch, return from lunch, depart at end of day.


I would like the spreadsheet to auto calculate each days hours worked, time at lunch, and overtime worked (based on an 8 hour day). I use military time but i can work with either.


I reviewed other posts and the Business template in Numbers for Employee Schedule does not function in this way, nor did the original question get an answered.

Auto Calculating Timesheet in Numbers


My prefered interface is ipad or iphone. Any pointers for a template or recommended equations?

iPad Pro 9.7-inch WiFi, iOS 11.0.1

Posted on Jun 4, 2018 4:33 AM

Reply
Question marked as Top-ranking reply

Posted on Jun 5, 2018 7:39 AM

Hi TheMissingBite,


Perhaps this will work for you

User uploaded file

There are no formulas in the "Data Entry" table.


In the "Summary" table,

Total Hours, Formula in B2 (and Fill Down) =Data Entry::E2−Data Entry::B2

Lunch Break, Formula in C2 (and Fill Down) =Data Entry::D2−Data Entry::C2

Working Hours, Formula in D2 (and Fill Down) =B2−C2

Overtime, Formula in E2 (and Fill Down) =D2−"8h"


Now apply the overtime rate 😎.

Regards,

Ian.

Similar questions

4 replies
Question marked as Top-ranking reply

Jun 5, 2018 7:39 AM in response to TheMissingBite

Hi TheMissingBite,


Perhaps this will work for you

User uploaded file

There are no formulas in the "Data Entry" table.


In the "Summary" table,

Total Hours, Formula in B2 (and Fill Down) =Data Entry::E2−Data Entry::B2

Lunch Break, Formula in C2 (and Fill Down) =Data Entry::D2−Data Entry::C2

Working Hours, Formula in D2 (and Fill Down) =B2−C2

Overtime, Formula in E2 (and Fill Down) =D2−"8h"


Now apply the overtime rate 😎.

Regards,

Ian.

Jun 6, 2018 1:03 AM in response to TheMissingBite

"I would like the spreadsheet to auto calculate each days hours worked, time at lunch, and overtime worked (based on an 8 hour day). I use military time but i can work with either."


Hi TMB


Here's a copy of the "Sign in" table from the Employee Schedule template (Numbers 3.6.2) with two columns added to record the beginning and end of the lunch break.

User uploaded file

Total hours are calculated in column G, with this formula:

G2: D2−C2+F2−E2


The formula requires a date and time entry in columns C, D, E and F, and will return an error if any of these four cells is empty or contains a different type of value.


The three lines differ only in the amount of time spent 'at lunch.'


Any hours, up to 8, are considered 'regular' time. Any hours more than 8 are considered overtime.


Regulr hours and OT hours are calculated with these two formulas in H2 and I2:


H2: MIN(G2,"8h")

I2: MAX(G2−"8h","0h")


Regards,

Barry

Jun 5, 2018 6:12 AM in response to SGIII

The purpose of the Employee Schedule spreadsheet is to create and build a schedule to coordinate several employees start/end times and lunches. The fixed lunch time reduces the flexibility of this spreadsheet as a time recording tool.


The tool that I believe Corrinna and I would like is spreadsheet to enter start/end times for a given day worked into a spreadsheet that calculates the duration of hours worked, at lunch, and overtime worked (daily, weekly/pay period).


I played with the equations and the duration crossing the 12-o-clock did not output the correct duration. Note the value in Hours (Combo Fx) cell.


Date

In

Out (Lunch)

In (Lunch)

Out (Lunch)

Hours (Morning)

Hours (Lunch)

Hours (Afternoon)

Hours (Combo Fx)

Overtime

6/4/18

7:00

11:00

11:30

16:30

4:00

0:30

-31651:00

-31647h 0m


6/5/18

7:00

10:15

11:15

17:30

3:15

1:00

-31649:45




Hours (Combo Fx)
(IF(OR(ISBLANK($In '6/4/18'),ISBLANK($C2)),DURATION(0), $C2−$In '6/4/18'))+(IF(OR(ISBLANK($'In (Lunch)' '6/4/18'),ISBLANK($E2)),DURATION(0), $E2−$'In (Lunch)' '6/4/18'))


I re-entered the end time and the output corrected, which was not intuitive.


Date

In (begin)

Out (Lunch)

In (Lunch)

Out (end)

Hours (Morning)

Hours (Lunch)

Hours (Afternoon)

Hours (Combo Fx)

Overtime

6/4/18

7:00

11:00

11:30

16:30

4:00

0:30

5:00

9h 0m


6/5/18

7:00

10:15

11:15

18:45

3:15

1:00

7:30

10h 45m


6/6/18

6:45

11:15

11:45

18:30

4:30

0:30

6:45

11h 15m



Now I need to summarize the results to show anything over 8 hours at overtime, such as a table with duration of regular hours, lunch, and overtime. Recommendations are welcome.


I'm still wondering where the time entry log template is in Numbers.

Auto Calculating Timesheet using Numbers App

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