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
[TABLE="width: 386"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]14:35[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]14:45[/TD]
[TD="align: right"]10 [/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]15:50[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]16:23[/TD]
[TD="align: right"]33 [/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]16:00[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]360[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]16:30[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]16:40[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]16:55[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]17:10[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]17:00[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]18:04[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]18:10[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]18:30[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]19:38[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]19:51[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]20:05[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]20:10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]20:28[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]20:32[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]21:00[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]21:20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]21:32[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]21:36[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]22:37[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]23:37[/TD]
[TD="align: right"]60[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]23:45[/TD]
[TD] 21/04/2014[/TD]
[TD="align: right"]23:50[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]21/04/2014[/TD]
[TD="align: right"]23:55[/TD]
[TD] 22/04/2014[/TD]
[TD="align: right"]00:05[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]22/04/2014[/TD]
[TD="align: right"]00:15[/TD]
[TD] 22/04/2014[/TD]
[TD="align: right"]00:25[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

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
Date	Time	Date2	Time2	Duration	CorrectedDuration

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]14:35[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]14:45[/TD]
[TD="align: right"]00:10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]15:50[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]16:23[/TD]
[TD="align: right"]00:33:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]16:00[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]22:00[/TD]
[TD="align: right"]06:00:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:30:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]16:30[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]16:40[/TD]
[TD="align: right"]00:10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:10:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]16:55[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]17:10[/TD]
[TD="align: right"]00:15:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:05:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]17:00[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]17:15[/TD]
[TD="align: right"]00:15:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:15:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]18:04[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]18:10[/TD]
[TD="align: right"]00:06:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:06:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]18:30[/TD]
[TD="align: right"]21/04/2014[/TD]
[TD="align: right"]19:00[/TD]
[TD="align: right"]00:30:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]00:30:00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

</tbody>

Timings​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=(C4+D4)-(A4+B4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"]=IF(OR(I4="REASON CODE",I4=0),"0",IF((C4+D4)>(A5+B5),(A5+B5)-(A4+B4),(C4+D4)-(A4+B4)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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