Creating a Timesheet

Question

Level 1

14 points

Creating a Timesheet

Hi there,

I hope everyone's day is going well so far! 🙂

I wanted to ask for help creating a timesheet for myself and a small group of people I work with. It's going to be pretty basic (I think) but here's what I'm looking for:

I want to have something that shows -

Employee name: John

Hourly rate: $25.00

Date	Log in	Log out	Total Hours	Total Pay
Monday	8:00am	4:00pm	8	$200
Tuesday	8:00am	4:00pm	8	$200
Wednesday
Thursday
Friday
Saturday
Sunday
TOTAL	16	$400

I will be adding a short part underneath this table above for projects that have been worked on a short description of what's been accomplished on a hourly type basis.

Can anyone help me with the formulas? 🙂

MacBook Pro with Retina display, OS X Yosemite (10.10.2)

Posted on Nov 16, 2015 11:45 AM

Reply

Answer 1

SGIII

Level 8

37,580 points

Nov 16, 2015 12:03 PM in response to anchor.v

If you haven't already, suggest you look at the Employee Schedule template at File > New, then choose this in the Business section the template browser:

SG

Reply

Answer 2

SGIII

Level 8

37,580 points

Nov 16, 2015 1:14 PM in response to anchor.v

Note the formula that Apple uses in the HOURS column of the Sign In table:

=IF(OR(ISBLANK(C2),ISBLANK(D2)),DURATION(0), D2−C2−Administration::A$2)

This is verbose and could be shortened but generally gets the job done. Perhaps you can work that into your document.

SG

Reply

Answer 3

anchor.v Author

Level 1

14 points

Nov 16, 2015 12:05 PM in response to SGIII

HAAAHHH... oh my goodness. 😟 It's the simplest things that get us. I spent the last hour working on one for myself, but finally got it about 5 minutes ago, and now you show me this lol.

Thanks! I believe this will work 😉

Reply

Answer 4

anchor.v Author

Level 1

14 points

Nov 16, 2015 12:18 PM in response to SGIII

Yeah it's pretty fantastic.

There is one thing, I'm trying to copy the "administration::A$2" formula into my Total Hours column without making cells negative if they don't have hours in them. Any ideas?

Reply

Answer 5

anchor.v Author

Level 1

14 points

Nov 16, 2015 12:26 PM in response to SGIII

It's actually on my own spreadsheet I have. I have this setup here:

But if I copy the E9 cell, it makes the whole column that I paste to -1 hour. How can I avoid that?

Reply

Answer 6

SGIII

Level 8

37,580 points

Nov 16, 2015 12:13 PM in response to anchor.v

It's pretty close, at least provides a great starting point. Post back if you have questions or run into problems implementing it.

SG

Reply

Answer 7

SGIII

Level 8

37,580 points

Nov 16, 2015 12:23 PM in response to anchor.v

Sorry, don't follow what you're asking. A$2 contains a duration of 1h for lunch break, not a formula, right? And I don't see any negatives in my template even in rows with no hours recorded.

SG

Reply