Missing dates

[tweacle361]

have a workbook of which I check every month for breaks in continuity on
a variety of machines. What im trying to do is see the breaks in
continuity when dates are missing.

I.E say I have a workbook which has start dates.end dates and machine
numbers

Machine 1 Start date= 01/03/2006 end date = 07/03/06 then on the next
entry Start 08/03 end 16/03. Obviously there no break there so that
fine.
Machine 2 Start 01/03 end 07/03 start 25/03 end 31/03. Obviously there
a break there and I need to be able to have that highlighted. I just
need it to highlight when there is a break in dates and not in between
2 dates.

There are about 40 machines which all have there own number. When their
input they are not in any order cos there input every time there
emptied. I only need to check the contunity monthly so if to make it
easier they can be sorted into machine no before checking for breaks.

Can anyone help?

 

[Ian]

I'm assuming columns A:C are machine, start, end and that row 1 is the
headers.
I'm also assuming you have sorted the data into machine/date order.

Firstly, using conditional formatting:

In C3, use Formula is =AND(A3=A2,B3<>C2+1) and copy down.

The problem with this is that, when you sort your data and row 2 changes
position, it will cause problems as there is no conditional formatting in
row 2 (or wherever it moves to). You would need to set the conditional
formatting AFTER sorting the data. I assume you don't want to do this.

Secondly, use another column to highlight the row.

In D3, enter =IF(AND(A3=A2,B3<>C2+1),"Break","") and copy down.

When you sort the data, sort only columns A:C. If there is other data in
columns after D, then insert a column before A and use the modified formula
=IF(AND(B3=B2,C3<>D2+1),"Break",""). You can then sort columns B:??.

 

[Toppers]

Assuming data is sorted by m/c and start date and data is:

Col A <m/c id>
Col B <start Date>
Col C <end date>

starting in row 2 then in row 3 put

=IF(A3=A2,IF(B3<>C2+1,"Break",""),"")

and copy down

HTH