Timesheet Overtime Math Question

[John_Krenkel]

I am trying to figure out a way to calculate base pay on a 40 hour work week and then overtime after that. I imagine i need the app to look across a week of data to determine how many hours have been worked:

If Week Hours <= 40, Hours worked x payrate, else Regular time = (40 * payrate), Overtime = Hours worked - 40*(1.5 * Payrate)

Any ideas how to do this?

I am at a bit of a loss.

[LeventK]

@John_Krenkel
It’s easy to calculate with AppFormulas actually. What’s your sheet structure and which columns do you have? If you can elaborate, we will be pleased to help you out.

[John_Krenkel]

Here are my columns. What I really need help on is how to look at time from the perspective of encapsulating in a one week time span. Any help in how or accumulate time for a week period would be great.

Date
Name
Emp Name
Email
Time In
Time Out
Regular Hours <= 40 hrs for the week
Overtime Hours > 40 hrs for the week
Total Hours
Hourly Wage (Probably can get rid of this since it is calling from another sheet)
Regular Pay (Payrate * regular hours)
Overtime Pay ((Payrate1.5) Overtime hours
Pay Period
Change TimeStamp

[Steve]

What is the key column?

What are Name and Emp Name?

Can a worker clock-in and -out more than once a day?

Can a Time In and Time Out be in different days (e.g., for work overnight)?

Are Total Hours, Regular Hours, Overtime Hours, Regular Pay, and Overtime Pay intended to reflect only the particular row, or should they reflect the weekly totals to date? For instance, would a row’s Total Hours column value show the hours contained in that row, or the total hours for the week up to and including that row?

Hourly wage should probably remain to reflect the wage at the time the work was done. If the wage used in calculations is pulled from elsewhere, you prior calculations will get confused if a pay rate is changed at a later time.

What is Pay Period? A date? A week number?

What day does a work week start on? Sunday? Monday?

[John_Krenkel]

Thank you for the questions.

Name and Empl name are the same thing. I think when I built this I was having an issue with one of them. I will have to go and look.

Yes an employee can (and does) clock in multiple times a day (ex. Lunch)

Total hours for that clock in. Was thinking off adding some sort of cumulative hours column that adds everything up for the week. The based on that, we could determine if hours for a row would be regular or OT.

Will keep hourly wage then.

Pay period was simply going to be a number. so if there were 24 pay periods, it would reflect which one we were on. We could change that to week number if needed.

I hope that helps.

To get a cumulative hours, we could use the week number and add the hours onto that.

Any how, i am open to ideas to fix or modify my tables.

John

[1minManager]

Hi John

I’ve done loads of these (bit of a speciality really) - https://1minmanager.com/timesheets/

The most complicated was where a client had 30+ users and a tablet on reception. Each user had a barcode and would swipe in and out. The App would calculate whether they were going in or out, their total wage for that day and do a wage calculation over a custom time period.

The hardest bit is having the App read backworks and forwards across rows to work out, in your case, if a time entry pushed someone into overtime.

Trying to advise specifically is very difficult for me without fully understanding how you pay them and what data structure you have. I’m UK based so happy to have a telephone chat if this would help?

Simon@1minManager.com