Squencial dating in excel
He has cleverly added code that will post the edit date.
It cannot be changed and is updated every time a record is added. This seems top work, but could I be missing something or employ a better approach?A related function is the DAYS function that can be used to find the number of days between two dates that occur in the same week or month using a subtraction formula as shown in row 9 of the example in the image above.The DAYS function was first introduced in Excel 2013.Date/times should be in ascending order and correspond with the ID. But the question could come to, which date was edited? [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]. If it really was not edited, then 4, 5, and 6 were edited and out of order.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. This pretty clearly shows the ID 7125 seems to have too high a date. If entry 3 was edited, then it and entry 4 and 5 are out of whack.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 .
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.
However, it is greater than C9 (7126), which means that the date/time was edited. Instead of 1/27/2012 AM I would place it as 01/26/2012 between and . I would speculate the date/time for 7125 was mistyped with the time correct and the wrong date. 4Option Explicit Private Sub Worksheet_Change(By Val Target As Range) '*** Limiting the Worksheet_Change event to a firing when a single cell is changed Dim isect As Range Dim z Curr Cell As String Set isect = Application.
Steve Hey Y'all, And then there's the case where... Intersect(Range("C: C"), Target) If isect Is Nothing Then Else z Curr Cell = Target. Value = "Edited: " & Format(Now(), "mm/dd/yy") Application. Row, 4) = False End If End If End Sub Thanks again, everyone.
I can’t imagine why you would need to, but you can do it with this formula.
If you are adding a month and your start date is the 29th, 30th, or 31st of the month, expect for February to give you some trouble.
Original Edited 3/1/2014 3/3/2014 3/2/2014 3/5/2014 The date has been edited but is still in sequence. I think what you really need is a Worksheet change event that will mark a date if it is edited. Address(, , xl A1) '***Prevent following code from refiring Change Event *** Application. Enable Events = True '*** Reset Events *** End If End Sub You could use a Worksheet_Change event to evaluate 3 states for column C: blank, original input (True), modified input (False). First, for some reason that must be user error, I can't get the macros to list or run in my book. 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. My problem is that I have an historical record and want to find possible edits.