Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    201
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Question 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!
    Attached Files Attached Files
    JimmyW
    Helena, MT

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    New Lounger
    Join Date
    Dec 2013
    Posts
    13
    Thanks
    1
    Thanked 3 Times in 3 Posts
    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.

  4. #3
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    201
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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.
    JimmyW
    Helena, MT

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,206
    Thanks
    14
    Thanked 331 Times in 324 Posts
    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

  6. #5
    New Lounger
    Join Date
    Dec 2013
    Posts
    13
    Thanks
    1
    Thanked 3 Times in 3 Posts
    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.
    Last edited by THill; 2014-03-22 at 14:46. Reason: Error in orginal post

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 765 Times in 699 Posts
    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
    Last edited by RetiredGeek; 2014-03-22 at 15:18.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  8. #7
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    201
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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
    JimmyW
    Helena, MT

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 765 Times in 699 Posts
    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
         zCurrCell = Target.Address(, , xlA1)
         '***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
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


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

    Maudibe (2014-03-22)

  11. #9
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,109
    Thanks
    39
    Thanked 197 Times in 184 Posts
    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
    Attached Files Attached Files

  12. #10
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,109
    Thanks
    39
    Thanked 197 Times in 184 Posts
    RG,

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

    Maud

    Update: RG, very nice!
    Last edited by Maudibe; 2014-03-22 at 15:52.

  13. #11
    3 Star Lounger Jimmy-W's Avatar
    Join Date
    Jan 2001
    Location
    Helena, Montana, USA
    Posts
    201
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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.
    Attached Images Attached Images
    JimmyW
    Helena, MT

  14. #12
    New Lounger
    Join Date
    Dec 2013
    Posts
    13
    Thanks
    1
    Thanked 3 Times in 3 Posts
    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.
    Last edited by THill; 2014-03-22 at 19:39.

  15. #13
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,109
    Thanks
    39
    Thanked 197 Times in 184 Posts
    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

  16. #14
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,058
    Thanks
    196
    Thanked 765 Times in 699 Posts
    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.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  17. #15
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,109
    Thanks
    39
    Thanked 197 Times in 184 Posts
    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 LastLast

Posting Permissions

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