excelvalidationtimeexcel-formulakpi

Excel: check hours in sequential order, same column, when criteria is true

I have a DB which contains shipping orders for different clients, one shipment can have more than one dispatch with different estimated arrival times, the logic is: dispatches on each route must follow a sequential order (06:00->06:30->08:00) and not (06:30->08:00->06:00) all times are in the same column but the route number is on another column. Validation must be done only for dispatches in the same route.

First I tried using a separate sheet with cells calculating the difference between each time and the next in the route but I get errors when the formula encounters blank cells. The formula was:

=IF(COUNTIFS($I$2:$I$2000;$I2;$A$2:$A$2000;$A$2)>1;$E3-$E2;0)

I used COUNTIFS() to validate the dispatch wan on the same route for the same dispatch center, since the DB consolidates 5 dispatch centers. The formula returned negative numbers when it detected an incorrect sequence, but it was detectting false positives between the last and first dispatches on adjacent routes.

I then tried to use conditional formatting to check this, using:

=IF(COUNTIFS($I$2:$I$2000;I2;$A$2:$A$2000;A2)>1;E3<E2;0)

However, something is not right... I would appreciate if someone could give me an insight to make this work, or if there is another way to do this validation (I am sure there is one, but I'm not yet aware of it). Please help.

UPDATE: Here is some sample data, as you can see the are times that are not sequential, but also false positives on last/first dispatches on each route

Sample data clarified, and it is showing the expected result, need to avoid the false positives. please help

Solution

  • Suppose you have the following named ranges:

    You can use the following formula as the formatting rule:

    =NOT(AGGREGATE(15,6,Col_E/(Col_A&Col_I=$A2&$I2),COUNTIFS($A$2:$A2,$A2,$I$2:$I2,$I2))=$E2)

    Highlight Column E with cell E2 being the active cell, go to conditional formatting to set up the above formatting rule, and you should have something like the following:

    Example

    Column J is for demonstration only. You do not need this column in your actual worksheet.

    The logic is to use Col_E/(Col_A&Col_I=$A2&$I2) to return the relevant range of time from Column E based on information in Column A and I, then use AGGREGATE function to return the nth smallest value from this range where nth is determined by the COUNTIFS formula, then compare the nth time with the actual time to find out if they are the same. If not that means a delivery was scheduled at a later time but positioned ahead of other deliveries that was scheduled at an earlier time, or vice versa (that's why both the ones that were delivered early with a later time and the ones that were delivered late with an earlier time are both highlighted in my example).

    Let me know if you have any questions. Cheers :)