# Thread: Function to find out-of-order dates (Excel 2010)

1. ## Function to find out-of-order dates (Excel 2010)

I need a little help to see whether I'm doing this correctly, and attached a sample spreadsheet. Column A (ID) is a sequential number, like a primary key. It cannot be changed and is updated every time a record is added. Deleting a record deletes the ID, which is not re-issued, so there may be missing numbers. Column B is the person's name, and Column C is the date and time entry. Date/times should be in ascending order and correspond with the ID. So, for example, as the date/time in C8 (ID 7125) is greater than or equal to C7 (7124), the first part of the test is okay. However, it is greater than C9 (7126), which means that the date/time was edited.

This is what I did:
1. Sort ID field smallest to largest.
2. Sort Date field oldest to newest
3. In D3 enter =AND(C3>=C2,C3>C4)
4. D8=TRUE, which indicates an out-of-order date.

This seems top work, but could I be missing something or employ a better approach? Lastly, after I'm done, is there a function through which I can find all or the TRUEs and highlight each such record? Thanks!

2. Here's a non-elegant but quick approach to finding all the TRUE's: copy and paste values from col. D to col. E, then sort the data on col. E (largest to smallest), and all the TRUE's will be at top.

3. Thanks! Yes, that seems quick and easy. However, I just found that I should use the =OR function instead of =AND. =OR(C3<C2,C3>C4). Also, I'm trying to consider whether the out-of-order dates can skew dates that precede or follow such dates. One thought: remove the out-of-order dates and run =OR again.

4. AND means all the conditions must be true for the total, with OR any TRUE part will make the entire item TRUE.

You can highlight the TRUEs with conditional formatting, or even add a filter and filter to only display the TRUE values (though you won't see the values around it if that is desired.

But the question could come to, which date was edited? Are you highlighting the proper one? I don't think there really is a way to tell.

I think an alternate method would be to plot an XY scatter chart with the ID as the X value and the date as the Y-value. [By convention it should be the other way, with the dependent ID on the Y-chart, but with more IDs and "larger width" of the dates, it looks better the other way, but it makes no difference in the analysis since you are only looking for odd points]. This pretty clearly shows the ID 7125 seems to have too high a date. Instead of 1/27/2012 9:51:41 AM I would place it as 01/26/2012 between 09:45:04 and 10:11:31. None of the other ones seem off to me. I would speculate the date/time for 7125 was mistyped with the time correct and the wrong date.

Steve

5. I see that there is a problem with either approach. How about sorting on col. C, the date/time field, then looking for IDs that are out of order, using the MAX function. So formula in D3 would be =A3<MAX(A\$2:A3). When you copy that down, the A\$2 of the range will stay the same, and the A3 will change to the current row.

If it's possible for subsequent values of the date/time to be identical, then the sort should be on col. C, then col. A.

6. Hey Y'all,

And then there's the case where...
Original Edited
3/1/2014
3/3/2014 3/2/2014
3/5/2014

The date has been edited but is still in sequence. So how do you find that one.

I think what you really need is a Worksheet change event that will mark a date if it is edited.

Code:
```Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'*** Limiting the Worksheet_Change event to a firing when a single cell is changed

Dim isect As Range

Set isect = Application.Intersect(Range("C:C"), Target)
If isect Is Nothing Then

Else
'***Prevent following code from refiring Change Event ***
Application.EnableEvents = False
ActiveCell.Offset(-1, 1).Value = "Edited: " & Format(Now(), "mm/dd/yy")
Application.EnableEvents = True '*** Reset Events ***
End If

End Sub```
Jimmy.JPG

NOTE: This only works if the user hits enter after the edit. I'm working on changes so it always works.
HTH

7. Thanks, guys. The MAX function in D3 reports an error of Formula Omits Adjacent Cells. The IDs are set and and every time entry originally is entered in that order. Some were edited later. Steve, I think you hit the biggest issue. Here's an example:

1. 04/30/2011 09:53:19
2. 04/30/2011 09:56:20
3. 05/02/2011 11:45:15
4. 04/30/2011 10:00:00
5. 04/30/2011 09:45:34
6. 05/02/2011 11:35:53

Entry 3 seems out of order. If it really was not edited, then 4, 5, and 6 were edited and out of order. If entry 3 was edited, then it and entry 4 and 5 are out of whack. 4>5 and 5<2. If I ignore the IDs, the order should be as follows, but then I can't tell whether any date was edited.

5 4/30/2011 9:45
1 4/30/2011 9:53
2 4/30/2011 9:56
4 4/30/2011 10:00
6 5/2/2011 11:35
3 5/2/2011 11:45

8. Jimmy,

This code works in all situations.
Code:
```Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'*** Limiting the Worksheet_Change event to a firing when a single cell is changed

Dim isect As Range
Dim zCurrCell As String

Set isect = Application.Intersect(Range("C:C"), Target)
If isect Is Nothing Then

Else
'***Prevent following code from refiring Change Event ***
Application.EnableEvents = False
Range(zCurrCell).Offset(0, 1).Value = "Edited: " & Format(Now(), "mm/dd/yy")
Application.EnableEvents = True '*** Reset Events ***
End If

End Sub```
HTH

9. ## The Following User Says Thank You to RetiredGeek For This Useful Post:

Maudibe (2014-03-22)

10. You could use a Worksheet_Change event to evaluate 3 states for column C: blank, original input (True), modified input (False).

Initial State: C2 is blank and D2 is blank
if Date entered in C2 and D2 is blank the D2=True (original value)
if Date is entered and D2 is True then D2=False (Modified)
If Date is entered and D2 is False then D2=False (Modified)

The following code will automatically evaluate the state of column C (to row 5000) when a date is entered change the adjacent cell in Column D to reflect its new state.

HTH,
Maud

Code:
```Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C1:C5000")) Is Nothing Then
If Cells(Target.Row, 4) = "" Then
Cells(Target.Row, 4) = True
ElseIf Cells(Target.Row, 4) = True Then
Cells(Target.Row, 4) = False
Else:
Cells(Target.Row, 4) = False
End If
End If
End Sub```

11. RG,

You were posting while I was still coding...sorry for duplicate technique but the methods are different.

Maud

Update: RG, very nice!

12. Thanks again, everyone. First, for some reason that must be user error, I can't get the macros to list or run in my .xlsm book. See the screenshot.

I think that we still have somewhat of a dilemma because any date entry can be edited and there is no way to tell which date was edited first if there is a series of anomalies. Yes, a worksheet change event is necessary. My problem is that I have an historical record and want to find possible edits. If I use =OR(C3<C2,C3>C4) and get a FALSE-TRUE-FALSE series in three consecutive records, it's very likely that the TRUE is an edited record. Where I have FALSE-TRUE-TRUE-FALSE requires visual inspection. Maybe I could:

1. =IF(C3<C2, TRUE, FALSE)
2. remove the first TRUE in any series of more than one TRUE
3. =IF(C3>C2, TRUE, FALSE)

#1 finds cases where a person who clocked on later (based on ID) now has a date that's earlier than the date before. If the instance is isolated, it's valid. In multiple (say 2) TRUEs, I can remove the first, assuming it's valid. I can run #1 again, and I then can run #3 to look for an instance where the person clocked in after date when the next ID clocked in. ID 1 cannot clock in after ID 2; however, ID 1's date can be edited to give that appearance. All dates and ID are set by the system.

13. Jimmy,

I believe the MAX function idea works in all cases, except where an ID's record is modified before the time that the next ID in sequence is originally input. The error reported may be because the range in each row of D is a different size. In any case, it can be ignored. (Highlight all cells in D, then ignore the error.)

Since the IDs are automatically assigned sequentially, and since the time stamp is also automatically generated, if you sort by the time stamp, then any ID that is less than the maximum value of the IDs above it must have been subsequently modified. In your second case above (where you sort by timestamp), IDs 1, 2, 4, and 3 had to be modified, since they originally had to be entered before 5's time of 4/30/2011 9:45. The only thing you can't determine for sure is when an ID's record is modified before the next ID in sequence is originally input. If that's likely to occur, then the macro approach makes sense.

14. Jimmy,

I think you should take a serious look at RG's solution. He has cleverly added code that will post the edit date. If you are looking for a complete audit trail, I am sure RG (as a suggestion) could modify his code to place each edit in adjacent cells. He has truly outdid himself with this one!

First, we have to get you able to rum macros. Check you security settings (2010): Options> Trust Center> Trust Center Settings> Adjust Macro Settings, Trust Locations, and Trusted Documents.

Maud

15. Jimmy,

The macro needs to be placed in the Sheet Object not the Workbook Object. If you do this they should run correctly. HTH

Maud,

Thanks for the kind words.

16. Sorry, didn't look at the picture you posted. You will also get that dialog box if you attempt to run the macro without having your cursor clicked somewhere within the macro code.

macros.png

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•