Creating numbers sheet for timesheet and income

Question

Level 1

8 points

Creating numbers sheet for timesheet and income

Hello, I am looking to create a timesheet with income in numbers. I have started with the ”employee schedule” template that is provided in the numbers app. But I would like to make a few modifications to what is there, but I don’t really know how to go about doing it…maybe with my screenshot I can convey what it is that I hope to achieve…so here goes.

First off I am the only employee and I am personally keeping up with this for my own records.

Box 1: This table is almost perfect the way it is. But this would just be the box that I would input the dates and times that I am working.

Box 2: Again this box is sorta the way I need it, but the adjustments to this may be what makes or breaks what I am trying to do. I would like for this box to reference the weeks in the year(so by the end of an entire year it would have 52 lines(Week 1-52) then have a total for the week(the way it currently is set up, it references Box 1 as if it were only made for 1 week at a time, and I don’t want to make 52 of these numbers spreadsheets per year.)

Box 3: Don’t necessarily need the graph but would like to have something that shows my year to date income. I don't make overtime so it doesn't need to be fancy and show different colors for each pay type.

I am aware that what I am asking for might not be possible but it is worth a shot to ask. If it cant be done with this particular template maybe someone has created something more like what I need, and can be pointed in that direction?

Thanks to anyone that might be willing to help out on this project!

iPad Pro, iPadOS 18

Posted on Nov 22, 2024 8:44 PM

Reply

Answer 1

Camelot

Level 9

55,305 points

Nov 25, 2024 11:26 AM in response to harcormor

What you're asking for is easily done via a Pivot Table.

For your use case with just one employee, it's simple, but the same setup could automatically tabulate multiple employees.

To make it easier, make a couple of changes to your existing Sign In table:

Add a new column C that calculates the Week Number for the date in column B using the formula:

=WEEKNUM(B2, Sunday is 1)

When done, you can hide this column if you don't want to see it, but this gives an easy way of mapping dates to weeks of the year.

Then, add a new column at the end that calculates the pay for that day. Since it's just you, you can simply multiply the Hours column by your pay rate (if you have multiple employees to track you'd use a LOOKUP() to find their pay rate).

Then you should end up with a table that looks like:

From here, select the table and choose Organize -> Create Pivot Table -> On Current Sheet (or on a new sheet if you prefer)

The key thing here is in the Inspector sidebar where you tell Numbers how to categorize the data.

For this setup, drag the Employee entry from the Fields to the Rows box.

Then drag the Week No. field to the Rows box, below the Employee tag.

This tells Numbers how you want to categorize the data - by Employee and Week Number

Then drag the Pay field to the Values.

You should end up with something that looks like:

and your table should look something like:

Done.

If you have multiple employee names, the week numbers will be repeated for each employee.

Reply

Answer 2

harcormor Author

Level 1

8 points

Nov 25, 2024 11:43 AM in response to Camelot

thanks for the response. That hurts my head reading that haha. But I will sit down and try to follow step by step what you said in just a bit. Is this easily done on iPad or would I be better off dusting off my 2013 MacBook Pro😂

Reply

Answer 3

Camelot

Level 9

55,305 points

Nov 25, 2024 1:48 PM in response to harcormor

it may sound like a lot of work, but it's actually pretty simple - I added step-by-step instructions since I'm not sure of your familiarity with the app so I didn't want to make assumptions, at the cost of making it look like more work than it really is :)

As for iPad vs. Mac... I admit I'm biassed towards the Mac version, but the same should be possible in the iPad.

If the Pivot Table steps are what's complicating things, you could also do this via just categorizing the data, but you'd still need to add the WEEKNUM() column to be able to group the shifts by week.

Reply