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?


--
tweacle361
------------------------------------------------------------------------
tweacle361's Profile: http://www.excelforum.com/member.php...o&userid=32202
View this thread: http://www.excelforum.com/showthread...hreadid=531321

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:??.


--
Ian
--
"tweacle361" wrote
in message ...

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?


--
tweacle361
------------------------------------------------------------------------
tweacle361's Profile:
http://www.excelforum.com/member.php...o&userid=32202
View this thread: http://www.excelforum.com/showthread...hreadid=531321

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

"tweacle361" wrote:


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?


--
tweacle361
------------------------------------------------------------------------
tweacle361's Profile: http://www.excelforum.com/member.php...o&userid=32202
View this thread: http://www.excelforum.com/showthread...hreadid=531321