Formula for Timesheet - Double Time, Time Half and normal Hours

Hi,

Just wanted to get some feedback on formulas for the new time sheet i am developing for a company.

I have attached a make shift one generated but i am at a blank at how to work out for time and a half and double time.

Quick overview is

Monday to Friday -
8 hours is at the normal RATE, first two hours after that is time and half and anytime from there is double time
SATURDAY
First 2 hours is time and half and anytime from there is double time
SUNDAYTIMESHEETS (2).xlsxTIMESHEETS (2).xlsx
All hours are double time

I will not be importing RATES at all as this is for the staff to fill in there whole hours for the day and i want to get it to manually gfenerate down the bottom so i have the amount of hours in the end.
I am happy to have to hide columns etc.

Any help would be great.

Thank you

This looks scarily similar to the problem in the link below, where a solution has been provided.

http://www.excelforum.com/excel-form...ime-2-0-a.html

Hi Quekbc,

thank you for the reply.

That is very 'scarily similar'. To mine. But one difference is that i do not get the time its self imported in by the staff. I only get them to insert the amount of hours worked as a whole number as they may do multiple jobs in the one day at different places

Any help would be appreciated. i have attached again.

Thank you again for the reply.TIMESHEETS (2).xlsx

Not a problem.

I notice that the days on row 4 are typed in as WEN, THUR, FRI, SAT, SUN, MON, TUE in that order. Is this always the case (i.e. consistent)? Or will they change from timesheet to timesheet?

Our working week starts on a Wednesday.

I have filled around a little and this is the new sheet,

but still at a blank to how to calculate it all.

Thank you again!

TIMESHEETS (version 1).xlsx

and sorry yes, that is always the case.

Hi Quekbc,

I can not thank you enough. i will look over this tonight and wrap my head around it all.

If i have any questions i will let you know in the morning.

Thank you again!

this looks perfect. But looking at it again it seems to not actually do what i needed it to do. I think it’s a little more complex then I first imagined.

my queries are;

the daily totals in row 22 is where we would need to pull the info from for the formulas you have come up with, to equal in the same sections C32:34. (if you could please redo that for me – as I have tried to copy your formulas to different rows but it has not worked).

That I think is this only way we will be able to at least get the totals correct at the bottom.

The idea was to try and avoid the staff writing in their overtime hours in rows 26:30 – But we also need to determine if the worker has allocated overtime hours on any day from Wednesday to Tuesday to individual jobs as it will then need to be billed to that job.

All in all overtime needs to be determined by day and by job on each day.

I have attached a new sheet with all the details.

Sorry for this, just want to make sure everything works.Copy of TIMESHEETS (version 1)_bc.xlsx

Hi Tracs. I understand the bit about getting RDO, Sick days and annual leaves into part of the calculation; that part is easy (see formula/attached below). However, I am not getting the bit about the OVERTIME section (row 26:30). What do you want seen there?

Formula:

Please Login or Register  to view this content.

Example is
On Wednesday – the staff member worked 15 hours. Therefore the staff member has worked 8 hours at normal time, 2 hours at time and half and 5 hours at double time. This needs to be worked out and shown (for all days) in rows 33:35.
In rows 24:30 –
Example shown –
On Wednesday the staff member worked at 4 different jobs. As you can see in rows 6,7,9,11. In rows 6, 9,11 they are at normal rate. But in ROW 7 (job 39412) – the job itself accrued 5 hours of overtime from that one staff member also. So the job (job 39412) will need to be billed the extra money for the OT the staff member had to work on that given day
-
Column K is just there as a whole figure – to see how many hours have been worked in the one job for the week. But the figure itself is not used for anything much. As you cannot determine the normal hours/overtime worked as it is across 7 different days.
--
It is a complicated option. And i don’t think there is a way around it really. Unless we change the whole format of the time sheet itself and add a whole lot of other options in.
----
Arranging the formula for rows 33:35. Might be the best option. And i will just get the staff to add in the overtime per job themselves.
By looking at the current sheet. I can tell on this given week this staff member has done the below

Normal Hours: 24
OVERTIME HOURS - TIME ½: 6
OVERTIME HOURS – DOUBLE: 14

also (RDO: 8 & SICK: 8 ) - which is working out OK itself.

Is there any chance we can make that be right?

Sorry if this is all a pain to you.TIMESHEETS (version 1)_bc3.xlsx

Okay, now I understand what you want there and I manage to get the 24/6/14 hours split that you mentioned above (see attached).

The only problem now, from my view is the OVERTIME section.

Why did you assume that Job 34912 is the one that should be incurred the overtime cost? The way I see it, is that the employee has worked on Wednesday, 15 hours. The overtime of 7 hours can be apportioned to any of them. Why just Job39412?

Another example is, consider Friday. Total hours worked is 11 hours, an overtime of 3 hours. Should this be noted within the OVERTIME section? If not, why? If so, how should the overtime be apportioned? Full 3 hours of Job39412? Or should it be "random" at row 10.

Please know that I'm asking these questions so I know which labels to pull into the OVERTIME section on cells A27:C31.

Hi,
Sorry i have been on holidays and have just come back.

I had a meeting yesterday with everyone for this sheet. They have raised a copy of issues. please see below comments and let me know if you think this can be arranged at all?

Bottom section will be calculated at ratios 1.5 hours for each hour for the first 2 hours then 2 hours for each hour after the first 2 hours. Jobs need to be allocated overtime hours specifically due to staying back at that job only (eg: After 3:30 I stayed at the last job for the day which was 33465, an additional 3 hours overtime and then went home).

The top section (normal/otdinary hours) each day will only ever have 8 hours total and will never have to be calculated at the ratio of 1.5 or 2 hours per 1 hour. (ie anything after 8 hours gets put into the overtime section)

Let me know your thoughts ..


thank you again!

I want to get this thread back up again:
I want to do a Timesheet workbook in Excel 2016.
The timesheet is for a group of people.
South African Basic Employment act prescribe overtime, double time and one-and-half time, night shift allowance. And I want to calculate them all in one sheet.
Because I cannot find a similar solution on the Internet, am I asking the question here.
The help that I need is with the formula to calculate NightShiftHours, DoubleTime and Overtime.
NightShiftHours = work between 18:00 and 6:00.
DoubleTime = work on Sunday and Public Holidays
Overtime = time and half for more work than 9 hours per day.