I have searched the net high and low for solutions to my problem with no luck and I would appreciate any of you experts that can guide me to a solution.
I am wanting to use one time-sheet for all employees
I have two different types of employees
Half hour paid lunch
Hour unpaid lunch
All employees have a choice on any hours worked beyond 8 hours in a day to be compensated through overtime pay, comp-time or can flex their time at time and a half meaning if they work an hour overtime on Monday, they can leave 1.5 hours early on Tuesday.
I have shifts 24/7 and need to properly calculate hours worked for shifts that start before midnight day1 and end on day2
Here are the cells I have on the spreadsheet that I need to work together
A1 = Start of Shift
B1 = Clock out for lunch or End of Shift
C1 = Clock back in to shift or Blank
D1 = End of Shift or Blank
The above cells are formatted h:mm
E1 = total hours worked subtracting unpaid lunch for those in this status
F1 = overtime adjusted total crediting time at 1.5 over 8 hours
Example:
A1 B1 C1 D1 E1 F1
22:00 01:00 02:00 08:00 9 9.5
22:00 01:00 02:00 07:00 8 8
22:00 01:00 02:00 05:00 6 6
I also need the formula to exclude C1 and D1 if they are blank for those
employees that get a 30 minute paid lunch.
Example:
A1 B1 C1 D1 E1 F1
22:00 07:00 9 9.5
I also need F1 to display the number of hours the same as E1 when they work less than their 8 hours shift
Now to top it off, I need F1 to round to the next quarter hour
I have had some limited success in getting some of these to work, but not all to work at the same time.
Can anyone help me with this? Please?
I am wanting to use one time-sheet for all employees
I have two different types of employees
Half hour paid lunch
Hour unpaid lunch
All employees have a choice on any hours worked beyond 8 hours in a day to be compensated through overtime pay, comp-time or can flex their time at time and a half meaning if they work an hour overtime on Monday, they can leave 1.5 hours early on Tuesday.
I have shifts 24/7 and need to properly calculate hours worked for shifts that start before midnight day1 and end on day2
Here are the cells I have on the spreadsheet that I need to work together
A1 = Start of Shift
B1 = Clock out for lunch or End of Shift
C1 = Clock back in to shift or Blank
D1 = End of Shift or Blank
The above cells are formatted h:mm
E1 = total hours worked subtracting unpaid lunch for those in this status
F1 = overtime adjusted total crediting time at 1.5 over 8 hours
Example:
A1 B1 C1 D1 E1 F1
22:00 01:00 02:00 08:00 9 9.5
22:00 01:00 02:00 07:00 8 8
22:00 01:00 02:00 05:00 6 6
I also need the formula to exclude C1 and D1 if they are blank for those
employees that get a 30 minute paid lunch.
Example:
A1 B1 C1 D1 E1 F1
22:00 07:00 9 9.5
I also need F1 to display the number of hours the same as E1 when they work less than their 8 hours shift
Now to top it off, I need F1 to round to the next quarter hour
I have had some limited success in getting some of these to work, but not all to work at the same time.
Can anyone help me with this? Please?
