Seeking help to format data with a macro

[holland]

Hello,

I have data for the start and stops times of journeys made by a car.

Here is an example

Attachment 156312

However, if an car is travelling at midnight (00:00) then the journey is split into 2 journeys. One journey ending at 23:59 and the other starting at 00:00 on the next day. See the cells in red.

I'm trying merge these two journeys into one journey (as it really is).

Attachment 156314

I was hoping that somebody might be able to help me with a macro to do this?

I'm looking to keep the first journey row (and make changes to it) and delete the second journey row.

I think the steps would be as follows:

1. Check check Column B for 00:00.

If this is found then, do the following

2. Change the stop date to the following day (i.e 15/01/2011 becomes 16/01/2011).
3. Change the stop time to the actual stop time (i.e 23:59:56 becomes 00:04:29).
4. Sum the two distances (i.e 8.355 + 2.052 = 10.387)
5 .Sum the two journey times (i.e 00:19:12 + 00:04:28 = 00:23:40).
6. Delete the second row.

I would be extremely grateful for any help with this.

Many thanks

John

[Bob Phillips]

Can't open the attachments, but you could try something like

=MOD(end_date_time-start_date_time,1)

[holland]

Hi,

Hopefully you can see the screenshot here?

http://dl.dropbox.com/u/54057365/All/pic1.JPG

http://dl.dropbox.com/u/54057365/All/pic2.JPG

[Bob Phillips]

I am saying you should have just one row of data. Move the end date and time from row 4 to row 3 and use

=MOD((C3+D3)-(A3+B3),1)