Creating new shifttime

[BBert]

I'm trying to create a new shifttime based on the original shifttime and
some overtime hours.
Let's say somebody works from 07:00 - 15:30 and is doing overtime from
15:30 - 16:30.
The new created shifttime should be displayed as 07:00 - 16:30.
Another example:
Original shift 14:00 - 22:00, overtime 12:00 - 14:00. New created
shifttime should be displayed as 12:00 - 22:00.

A similar question is when you take back some hours from the original
shifttime. For example, original shift 12:00 - 20:00, take back 18:00 -
20:00, new created shifttime should be displayed as 12:00 - 18:00.

Is there a function to accomplish that?

with kind regards,
Bert

 

[Guest]

How are your times stored in Excel?

Assuming original shift start/end are columns A & B, with OT start/end in
B&D then for EXTENDED shifts:

new Shift start =MIN(A2,C2)
new Shift end=MAX(B2,D2)

However, I don't know how you represent the reduced shift i.e take back
hours. To use the above formula, and with your example, the OT end time would
be 18:00 (and make OT start time the same i.e. 1800 ?)

HTH

 

[BBert]

How are your times stored in Excel?

Oké Toppers, you pushed me in the right direction. But as you pointed
out, the formula gives unpredictable results when used in the takeback
hours. But we can work that around.

All times are stored in single columns (single cells). That way it's
easier to determine if somebody is doing overtime or take back hours (i
don't know how you say that in plain English) and we don't have to
juggle with complicated formula's.

Let's assume this is my layout:
sh stands for shift, ot is overtime, tb is takeback
ST is Starttime, EN is Endtime.

A B C D E F G H
1 shST shEn otST otEN tbST tbEN shST shEN
2 7:00 15:30 6:00 7:00 6:00 15:30
3 12:00 20:00 20:00 23:00 12:00 23:00
4
5 14:00 22:00 21:30 22:00 14:00 21:30
6 13:00 21:00 13:00 13:30 13:30 21:00

Formula's used:
Column G Column H
=IF(A2=D2,C2,A2) =IF(B2=C2,D2,B2)
=IF(A3=D3,C3,A3) =IF(B3=C3,D3,B3)

=IF(A5=E5,F5,A5) =IF(B5=F5,E5,B5)
=IF(A6=E6,F6,A6) =IF(B6=F6,E6,B6)

I think this gets the job done. The only problem is when takeback hours
is within the shift. That would give me a split! Perhaps 2 extra columns
(I:J) would do the trick. Same problem when the overtimes are not
adjacent the shifttimes. Any thoughts.....?

Kind regards,
Bert