Removing Duplicate Timings Formula/VBA

[GarethT]

Hi All,

I have a small issues. My guys record downtime at a station, but because their stations are so far apart sometimes they duplicate this time. I was wondering how I could remove the duplicate timings. My report is as follows;
A B C D E
Date Time Date Time

21/04/2014	14:35	21/04/2014	14:45	10
21/04/2014	15:50	21/04/2014	16:23	33
21/04/2014	16:00	21/04/2014	22:00	360
21/04/2014	16:30	21/04/2014	16:40	10
21/04/2014	16:55	21/04/2014	17:10	15
21/04/2014	17:00	21/04/2014	17:15	15
21/04/2014	18:04	21/04/2014	18:10	6
21/04/2014	18:30	21/04/2014	19:00	30
21/04/2014	19:38	21/04/2014	19:51	13
21/04/2014	20:05	21/04/2014	20:10	5
21/04/2014	20:28	21/04/2014	20:32	4
21/04/2014	21:00	21/04/2014	21:20	20
21/04/2014	21:32	21/04/2014	21:36	4
21/04/2014	22:37	21/04/2014	23:37	60
21/04/2014	23:45	21/04/2014	23:50	5
21/04/2014	23:55	22/04/2014	00:05	10
22/04/2014	00:15	22/04/2014	00:25	10

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

I so far have come up with

=IF(C2=C3,(IF(OR(I3="REASON CODE",I3=0),"0",IF(D2>B3,(E2+E3)-E2,"0"))),0)

But it comes up with minus figures for the 2nd row and will only subtract 10 minutes from the 3rd row

Really stuck on this one, any help appreciated

Gareth

 

[sijpie]

I don't see any duplicate timings in your table.

What is it you wnat to do, to remove (a row with ) duplicate times or to change the time in that row.
Perhaps you can illustrate with a table before and after.

 

[GarethT]

I would like to change the time in that row. I mean that the timings are counted twice in rows 2 and 3.

 

[sijpie]

I think this is what you are after. The corrected duration column can replace your column E

Excel 2010

	A	B	C	D	E	F	G	H	I
3	Date	Time	Date2	Time2	Duration		CorrectedDuration
4	21/04/2014	14:35	21/04/2014	14:45	00:10:00		00:10:00		1
5	21/04/2014	15:50	21/04/2014	16:23	00:33:00		00:10:00		1
6	21/04/2014	16:00	21/04/2014	22:00	06:00:00		00:30:00		1
7	21/04/2014	16:30	21/04/2014	16:40	00:10:00		00:10:00		1
8	21/04/2014	16:55	21/04/2014	17:10	00:15:00		00:05:00		1
9	21/04/2014	17:00	21/04/2014	17:15	00:15:00		00:15:00		1
10	21/04/2014	18:04	21/04/2014	18:10	00:06:00		00:06:00		1
11	21/04/2014	18:30	21/04/2014	19:00	00:30:00		00:30:00		1

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Timings​

Worksheet Formulas Cell Formula E4 =(C4+D4)-(A4+B4) G4 =IF(OR(I4="REASON CODE",I4=0),"0",IF((C4+D4)>(A5+B5),(A5+B5)-(A4+B4),(C4+D4)-(A4+B4))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The formula includes the date, so you don't need to check if the dates are different