On Mon, 7 May 2007 07:02:00 -0700, Toppers wrote...
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