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.