Tracking Date Changes

[Guest]

I have a cell in which a user enters a completion date. I would like to be
able to track 1) the number of times the user changes the date in this cell
and 2)by how much he changes the date each time and keep a history of this
(ex 20 day slip, 30 day slip etc.)

Is there a way to do this in Excel?

Thanks in advance.

 

[Otto Moehrbach]

Lee
You could use a Worksheet_Change event macro that would "see" every
change made to that cell. That same macro could also "see" what the cell
contained before the change and place what you want in some hidden sheet
(date, time, amount of slip, etc). Is this something like what you want?
Post back if you need help setting this up. Include details about the
file/sheet setup and if there is more than one such cell you want to track.
HTH Otto

 

[Guest]

Thanks, some more details are

I am interested in tracking how many times they slip a date they have
originally committed to. It sounds like using Worksheet_Change event might
do the job. What I would really like to show from that data is to have two
other cells in the same worksheet display 1) the number of times they have
changed the date i.e. 1,2,3 etc., and 2) the total slip in days from the
original date they entered in the workbook.

 

[Otto Moehrbach]

Lee
This macro does what you want if I am reading you correctly. As
written, this macro assumes and does as follows:
The date that gets changed is in A1.
On the same sheet, C2 and D2 will display the number of times the date has
been changed and the total number of days slipped, respectively.
There must be a sheet named "Utility".
In the Utility sheet, row 1 is for the column headers.
In the first empty row of the Utility sheet, this macro will place the date
and time of the current change of date in Column A, and the number of days
slipped in Column B.
Note that this macro must be placed in the sheet module of the sheet that
contains the changed date in A1. To access that module, right-click on the
sheet tab, select View Code, and paste this macro into the displayed module.
"X" out of the module to return to your sheet.
If you wish, send me an email and I'll send you the small file I used to
develop this code. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldDate As Date
Dim NewDate As Date
Dim RngColAUtil As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address(0, 0) = "A1" Then
Application.ScreenUpdating = False
NewDate = Target.Value
Application.EnableEvents = False
Application.Undo
OldDate = Range("A1").Value
Range("A1").Value = NewDate
With Sheets("Utility")
.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Now
Set RngColAUtil = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
RngColAUtil(RngColAUtil.Count).Offset(, 1) = NewDate - OldDate
End With
[C2].Value = RngColAUtil.Count
[D2].Value = Application.Sum(RngColAUtil.Offset(, 1))
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

 

[Guest]

Otto,

I am pretty new to VBA but I did the view code and associated it with a
worksheet and add a blank worksheet called Utility. But when I type in a
date to A1 and the macro runs presumable as I am getting no error nothing
happens. I do not get anything appearing on the utility worksheet.

Do you have any ideas why this may not be working??

Thanks,

Lee
This macro does what you want if I am reading you correctly. As
written, this macro assumes and does as follows:
The date that gets changed is in A1.
On the same sheet, C2 and D2 will display the number of times the date has
been changed and the total number of days slipped, respectively.
There must be a sheet named "Utility".
In the Utility sheet, row 1 is for the column headers.
In the first empty row of the Utility sheet, this macro will place the date
and time of the current change of date in Column A, and the number of days
slipped in Column B.
Note that this macro must be placed in the sheet module of the sheet that
contains the changed date in A1. To access that module, right-click on the
sheet tab, select View Code, and paste this macro into the displayed module.
"X" out of the module to return to your sheet.
If you wish, send me an email and I'll send you the small file I used to
develop this code. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldDate As Date
Dim NewDate As Date
Dim RngColAUtil As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address(0, 0) = "A1" Then
Application.ScreenUpdating = False
NewDate = Target.Value
Application.EnableEvents = False
Application.Undo
OldDate = Range("A1").Value
Range("A1").Value = NewDate
With Sheets("Utility")
.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Now
Set RngColAUtil = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
RngColAUtil(RngColAUtil.Count).Offset(, 1) = NewDate - OldDate
End With
[C2].Value = RngColAUtil.Count
[D2].Value = Application.Sum(RngColAUtil.Offset(, 1))
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

Thanks, some more details are

I am interested in tracking how many times they slip a date they have
originally committed to. It sounds like using Worksheet_Change event
might
do the job. What I would really like to show from that data is to have
two
other cells in the same worksheet display 1) the number of times they have
changed the date i.e. 1,2,3 etc., and 2) the total slip in days from the
original date they entered in the workbook.

 

[Otto Moehrbach]

Lee
My Outlook Express is not displaying all of our messages. If you wish,
email me and we'll work it out that way. I still have the file I used to
develop the code. My email address is (e-mail address removed). Remove the
"nop" from this address. HTH Otto

Otto,

I am pretty new to VBA but I did the view code and associated it with a
worksheet and add a blank worksheet called Utility. But when I type in a
date to A1 and the macro runs presumable as I am getting no error nothing
happens. I do not get anything appearing on the utility worksheet.

Do you have any ideas why this may not be working??

Thanks,

Lee
This macro does what you want if I am reading you correctly. As
written, this macro assumes and does as follows:
The date that gets changed is in A1.
On the same sheet, C2 and D2 will display the number of times the date
has
been changed and the total number of days slipped, respectively.
There must be a sheet named "Utility".
In the Utility sheet, row 1 is for the column headers.
In the first empty row of the Utility sheet, this macro will place the
date
and time of the current change of date in Column A, and the number of
days
slipped in Column B.
Note that this macro must be placed in the sheet module of the sheet that
contains the changed date in A1. To access that module, right-click on
the
sheet tab, select View Code, and paste this macro into the displayed
module.
"X" out of the module to return to your sheet.
If you wish, send me an email and I'll send you the small file I used to
develop this code. HTH Otto
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldDate As Date
Dim NewDate As Date
Dim RngColAUtil As Range
If Target.Count > 1 Then Exit Sub
If IsEmpty(Target.Value) Then Exit Sub
If Target.Address(0, 0) = "A1" Then
Application.ScreenUpdating = False
NewDate = Target.Value
Application.EnableEvents = False
Application.Undo
OldDate = Range("A1").Value
Range("A1").Value = NewDate
With Sheets("Utility")
.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Now
Set RngColAUtil = .Range("A2", .Range("A" &
Rows.Count).End(xlUp))
RngColAUtil(RngColAUtil.Count).Offset(, 1) = NewDate -
OldDate
End With
[C2].Value = RngColAUtil.Count
[D2].Value = Application.Sum(RngColAUtil.Offset(, 1))
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

Thanks, some more details are

I am interested in tracking how many times they slip a date they have
originally committed to. It sounds like using Worksheet_Change event
might
do the job. What I would really like to show from that data is to have
two
other cells in the same worksheet display 1) the number of times they
have
changed the date i.e. 1,2,3 etc., and 2) the total slip in days from
the
original date they entered in the workbook.

:

Lee
You could use a Worksheet_Change event macro that would "see"
every
change made to that cell. That same macro could also "see" what the
cell
contained before the change and place what you want in some hidden
sheet
(date, time, amount of slip, etc). Is this something like what you
want?
Post back if you need help setting this up. Include details about the
file/sheet setup and if there is more than one such cell you want to
track.
HTH Otto
I have a cell in which a user enters a completion date. I would like
to
be
able to track 1) the number of times the user changes the date in
this
cell
and 2)by how much he changes the date each time and keep a history
of
this
(ex 20 day slip, 30 day slip etc.)

Is there a way to do this in Excel?

Thanks in advance.