OP said:
Based on certain times i work i get Time/time*1.5/time*2 [....]
Between 8 and 5 its normal time.
Between 17:00:01 - 21:00 its Time*1.5
After 21:00 - 08:00 the following day its Time*2
How can i automate this?
Columns:
a1 - Date
b1 - Day
c1- Start Time [hh:mm]
d1- End Time [hh:mm]
e1 - Break
e1 - Total Hours (=SUM(D2-C2)-E2) ?
f1 - Overall Time (Automated to include overtime)
Too complicated? Is this possible?
A bit more complicated than necessary. The issues are:
1. It would be better if the date were included with the times in C1 and D1,
even if you choose to format them as hh:mm.
Alternatively, it would be better if work periods were recorded as midnight
to 23:59 of the same day. Thus, a swing shift (across midnight) would be
recorded as __two__ work periods, one for each day. That might result in
two work periods recorded for the same day.
2. I presume "Break" is also in the form h:mm. It would be better if we had
"start break" and "end break" times; and even better if they included the
date.
Otherwise, we are left to guess how to apply break time to regular time,
"1.5 time" and "2.0 time".
3. What's the difference between "total hours" and "overall time"? I
suspect the latter is intended to be the "time factor" that can be
multiplied by hourly wage.
And as such, I presume it should be the decimal number of hours, not [h]:mm.
4. You have two columns identified as E1.
5. I assume the above are titles in row 1, and the data starts in row 2.
Based on some assumptions....
1. Total time (F2; format as h:mm):
=MAX(0,D2-C2+(D2<=C2)-E2)
(D2<=C2) handles the case where we start in one day and finish the next day.
MAX(0,...) handles the unlikely case when the break time exceeds the total
time (a recording error).
2. Total regular time (G2; format as h:mm):
=MAX(0,MIN(D2+(D2<=C2),TIME(17,0,0))-MAX(C2,TIME(8,0,0)))
+MAX(0,MIN(D2+(D2<=C2),1+TIME(17,0,0))-MAX(C2,1+TIME(8,0,0)))
The second MAX(0,...) handles the case where we work parts of split regular
shifts, e.g. 16:00 one day to 16:00 the next day.
3. Total 1.5 time (H2; format as h:mm):
=MAX(0,MIN(D2+(D2<=C2),TIME(21,0,0))-MAX(C2,TIME(17,0,0)))
+MAX(0,MIN(D2+(D2<=C2),1+TIME(21,0,0))-MAX(C2,1+TIME(17,0,0)))
The second MAX(0,...) handles the case where we work parts of split 1.5 time
shifts, e.g. 20:00 one day to 20:00 the next day.
4. Total 2.0 time (I2; format as h:mm):
=MAX(0,MIN(D2+(D2<=C2),1+TIME(8,0,0))-MAX(C2,TIME(21,0,0)))
+MAX(0,MIN(D2+(D2<=C2),TIME(8,0,0))-MAX(C2,0))
The second MAX(0,...) handles the case where we work parts of split 2.0 time
shifts, e.g. 7:00 one day to 7:00 the next day.
5. Regular break (J2; format as h:mm):
=MIN(E2,G2)
6. 1.5 break (K2; format as h:mm):
=MIN(E2-J2,H2)
7. 2.0 break (L2; format as h:mm):
=MIN(E2-J2-K2,I2)
8. Time factor (M2; format as Number):
=(G2-J2+(H2-K2)*1.5+(I2-L2)*2)*24
9. Total pay (N2):
=ROUND(M2*$X$1,2)
assuming X1 contains the hourly rate.
Note: The break times in #5 through #7 are probably sufficient and
reasonable. They assume that breaks are taken first during regular time,
then 1.5 time, then 2.0 time. That assumption is probably wrong with the
day starts in the evening shift (17:00 and later). Also, the assumption has
"odd" results if the work day covers two parts of split regular shifts, for
example. All of the break time might be charged as regular time, which a
more reasonable assumption might charge the break time to the evening and/or
swing shifts.
