Over time?
 

Ladies & Gents...Boys and Girls - Cats and Dogs, hope your all well!

I need some assistance please.

[Caveat] - My excel skills are rubbish when it comes to mathematics [Caveat /]

Based on certain times i work i get Time/time*1.5/time*2

So...

Example:

Saturday
Working from 12:00pm - 23:59 (11:59 Hours)

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?

=IF(AND(OR may be required?

Many thanks in Advance..

If you talk bad about country music, it's like saying bad things about my momma. Them's fightin' words.
Dolly Parton

Over time?
 

OP wrote:
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 a


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.

Over time?
 

Improvement/errata.... I wrote:
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)))
[....]
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)))
[....]
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))

Simpler, with format correction and correction in #4:

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)))
+ IF(D2<=C2,MAX(0,MIN(D2,TIME(17,0,0))-TIME(8,0,0)))

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)))
+ IF(D2<=C2,MAX(0,MIN(D2,TIME(21,0,0))-TIME(17,0,0)))

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))-C2)
+ IF(D2<=C2,MAX(0,D2-TIME(21,0,0)))

Hi joeu2004[_2_], Thanks for the time you spent on this, really appreciated

Apologies for vague requirements - excel is not my greatest strength

I'm about compile it...i'll let you know how it goes.

Thanks

Dol

ok - its don't seem to be working out the 1.5/2.0 etc.

Answer to Assumption:
1. I agree. Remove the Date and day and the start time includes all:

dddd dd/mm/yyyy hh:mm
And i will create a new entry when i go past 23.59.



2. I presume "Break" is also in the form h:mm. YES

It would be better if we had
"start break" and "end break" times; and even better if they included the
date.

ADDED "start break" and "end break" times;


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.
Wanted a figure which excluded the 1.5/2.0 calculations and overall time to include 1.5 and 2.0


4. You have two columns identified as E1. - Typo.


5. I assume the above are titles in row 1, and the data starts in row 2. Yes


Ive attached - rename to from txt to .xlsx