I need a Timekeeping Formula

[samantha]

Hi
I need to create a timesheet to calculate normal time, overtime@1.5 and overtime@2.00 based on the days of the week and the job description
if the employee is a general worker, firewatcher then they qualify for overtime, all other employees do not qualify for overtime
Monday - Thursday - 9 hours = normal time - any hours clocked after 9 hours is calculated overtime @ 1.5
Friday - 5 hours = normal time - any hours clocked after 5 hours is calculated as overtime @ 1.5
Saturdays - all hours clocked on a Saturday is calculated as overtime @ 1.5
Sundays - all hours clocked on a Sunday is calculated as overtime @ 2
If an employee works nightshift on a Sunday and the hours run into a Monday all hours clocked are calculated as overtime @ 2

The day of the week is in cell c2 and the job description is in cell b5
the time in will be captured in cell c5 and the time out will be captured in cell d5
The Normal hours will be displayed cell e5
The OT 1.5 will be displayed in cell f5
the OT 2 will be displayed in cell G5
the total hours will be displayed in cell h5

I need a formula for
to check the days of the week and the job description and calculated the hours based on the criteria above.

 

[Deepak]

Can we have sample xl sheet with said query by manual approach.

 

[samantha]

Hi
Thank you for helping me. I have attached the file

 

[samantha]

Hi can anyone help me:
I have attached another example of the spreadsheet

 

[David Evans]

OK - I think I've got your hours sorted, but I'm not clear on which workers qualify for overtime ...

I've created a Rules table that holds the overtime criteria - give us a little more information on who qualifies for O/T and that can be added to the Rules table ...

Take a look at this as a first pass, and come back with your thoughts/comments/ideas .... I have not tested it thoroughly as I have to get on with something else here, but at first glance it appears OK ...

Others will be along shortly to help I'm sure!

 

[vletm]

@samantha
I've some questions:
A) row 14: Gate Watcher's hours: 7:00PM - 12:00PM 12:30PM-9:00AM?
1) 7PM - 12PM, means 4hrs for Sunday and 12hrs for Monday
2) lunch time
3) 12:30PM - 9AM, means 11:30hrs for Monday and 9hrs for Tuesday
Did I miss something?
Would You like to use VBA for this timekeeping?
It's easier to solve overnight workingtimes.

B) How do You 'mark' those who are overtime qualified?

C) working times: 6AM - 2PM 3PM - 8:45PM =>
| Normal | OT 1,5 | OT 2 | Total
Mo - Th | 9:00 | 4:45 | | 13:45
Fri | 5:00 | 8:45 | | 13:45
Sat | 13:45 | 13:45 | | 13:45
Sun | 13:45 | |13:45| 13:45
or do You mean that 'Total' would be counted like
Mo - Th 9:00 + 4:45*1.5
Fri 5:00 + 8:45*1.5
Sat 13:45 + 13:45*1.5
Sun 13:45 + 13:45*2

 

[vletm]

I found those 'overtime qualified'.

I think/hope that Gate Watcher and Technician hours are like this?
'Normal Hours' looks same. There should be some 'HrsOT*2'.
... and why 'Total Hours'? 'Total hours' are same as 'Total Hrs W/o Lunch'.
I would like to say, You should do this with Macro(s) and with different layout.
Ideas?

 

[RayLim]

Hi, I'm looking for something similar that calculates the normal hours and overtime. I need to track the total hours work by project. File as per attached. I'm using IF, AND, OR, WEEKDAY formula, not the best way that I could come up with. Appreciate any help I can get. Thank you.

 

[vletm]

Something like this?
and solved with times!

 

[RayLim]

Something like this?
and solved with times!

Thank you