Hi all,

I am using office 2003; I am trying to fix up a automated time tracking
system.

I like to find when the particular field is updated in the excel and
update the same in the adjacent row. Let me give you a example

Field A5 will be filled as Yes / No I want the A6 to be filled with the
date and time of the field A5 got updated

I tried with this formula: *@ A6 *I typed -=
IF(ISBLANK(A5)=FALSE,NOW()," ")

but the problem is this when ever I save the excel the A6 is getting
updated with the current time.

With regards
Pradeep


--
Qlychap
------------------------------------------------------------------------
Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253
View this thread: http://www.excelforum.com/showthread...hreadid=550268

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

Qlychap wrote:

Hi all,

I am using office 2003; I am trying to fix up a automated time tracking
system.

I like to find when the particular field is updated in the excel and
update the same in the adjacent row. Let me give you a example

Field A5 will be filled as Yes / No I want the A6 to be filled with the
date and time of the field A5 got updated

I tried with this formula: *@ A6 *I typed -=
IF(ISBLANK(A5)=FALSE,NOW()," ")

but the problem is this when ever I save the excel the A6 is getting
updated with the current time.

With regards
Pradeep

--
Qlychap
------------------------------------------------------------------------
Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253
View this thread: http://www.excelforum.com/showthread...hreadid=550268

--

Dave Peterson

Hi
Paste the code onto sheet module
1) right click on the sheet tab in question and select [ViewCode]
2) paste the code onto the blank space on the right pane
3) click x to close the window and get back to Excel

It will change the time stamp when the data actually changed

Code:
--------------------

Private OldData

Private Sub Sheet_SelectionChange(ByVal Target As Range)
OldData = Target.Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
With Target.Cells(1,1)
If .Address(0,0) < "A5" Then Exit Sub
If .Value = OldData Then Exit Sub
Application.EnableEvents = False
.Offset(1).Value = Now
Application.EnableEvents = True
OldData = .Value
End With
End Sub

--------------------


--
jindon
------------------------------------------------------------------------
jindon's Profile: http://www.excelforum.com/member.php...o&userid=13135
View this thread: http://www.excelforum.com/showthread...hreadid=550268

I used the McGimpsey code, and it worked, until I protected the sheet, then
it quit working entirely. I unprotected the 2 columns in the formula, but it
made no difference. Is there a trick to using it in a protected sheet?

Richard


"Dave Peterson" wrote:

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

Qlychap wrote:

Hi all,

I am using office 2003; I am trying to fix up a automated time tracking
system.

I like to find when the particular field is updated in the excel and
update the same in the adjacent row. Let me give you a example

Field A5 will be filled as Yes / No I want the A6 to be filled with the
date and time of the field A5 got updated

I tried with this formula: *@ A6 *I typed -=
IF(ISBLANK(A5)=FALSE,NOW()," ")

but the problem is this when ever I save the excel the A6 is getting
updated with the current time.

With regards
Pradeep

--
Qlychap
------------------------------------------------------------------------
Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253
View this thread: http://www.excelforum.com/showthread...hreadid=550268

--

Dave Peterson

First, unprotect the worksheet.
then format the column getting the time/date the way you want.

Then protect the worksheet once again.

And remove the formatting from the code.

Changing the format of a cell on a protected sheet could be causing your
trouble.

If that doesn't work, post the code you used and indicate the line that fails.

Richard R wrote:

I used the McGimpsey code, and it worked, until I protected the sheet, then
it quit working entirely. I unprotected the 2 columns in the formula, but it
made no difference. Is there a trick to using it in a protected sheet?

Richard

"Dave Peterson" wrote:

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

Qlychap wrote:

Hi all,

I am using office 2003; I am trying to fix up a automated time tracking
system.

I like to find when the particular field is updated in the excel and
update the same in the adjacent row. Let me give you a example

Field A5 will be filled as Yes / No I want the A6 to be filled with the
date and time of the field A5 got updated

I tried with this formula: *@ A6 *I typed -=
IF(ISBLANK(A5)=FALSE,NOW()," ")

but the problem is this when ever I save the excel the A6 is getting
updated with the current time.

With regards
Pradeep

--
Qlychap
------------------------------------------------------------------------
Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253
View this thread: http://www.excelforum.com/showthread...hreadid=550268

--

Dave Peterson


--

Dave Peterson

I unprotected the sheet, formatted the coulmn, inserted the code, and it
worked fine. I protected the sheet, and the code stopped running at
..NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the
sheet, not only do I not get an error, but nothing happens at all. I would
like to insert the date in mm/dd/yy format. Code follows

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Thanks
Richard


"Dave Peterson" wrote:

First, unprotect the worksheet.
then format the column getting the time/date the way you want.

Then protect the worksheet once again.

And remove the formatting from the code.

Changing the format of a cell on a protected sheet could be causing your
trouble.

If that doesn't work, post the code you used and indicate the line that fails.

Richard R wrote:

I used the McGimpsey code, and it worked, until I protected the sheet, then
it quit working entirely. I unprotected the 2 columns in the formula, but it
made no difference. Is there a trick to using it in a protected sheet?

Richard

"Dave Peterson" wrote:

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

Qlychap wrote:

Hi all,

I am using office 2003; I am trying to fix up a automated time tracking
system.

I like to find when the particular field is updated in the excel and
update the same in the adjacent row. Let me give you a example

Field A5 will be filled as Yes / No I want the A6 to be filled with the
date and time of the field A5 got updated

I tried with this formula: *@ A6 *I typed -=
IF(ISBLANK(A5)=FALSE,NOW()," ")

but the problem is this when ever I save the excel the A6 is getting
updated with the current time.

With regards
Pradeep

--
Qlychap
------------------------------------------------------------------------
Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253
View this thread: http://www.excelforum.com/showthread...hreadid=550268

--

Dave Peterson


--

Dave Peterson

You skipped this line:

And remove the formatting from the code.

Just delete that .numberformat line from your code.

And format that column G as mm/dd/yyyy (manually).

Richard R wrote:

I unprotected the sheet, formatted the coulmn, inserted the code, and it
worked fine. I protected the sheet, and the code stopped running at
.NumberFormat = "dd mmm yyyy hh:mm:ss". Even better, if I then unprotect the
sheet, not only do I not get an error, but nothing happens at all. I would
like to insert the date in mm/dd/yy format. Code follows

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Range("F2:F950"), .Cells) Is Nothing Then
Application.EnableEvents = False
If IsEmpty(.Value) Then
.Offset(0, 1).ClearContents
Else
With .Offset(0, 1)
.NumberFormat = "dd mmm yyyy hh:mm:ss"
.Value = Date
End With
End If
Application.EnableEvents = True
End If
End With
End Sub

Thanks
Richard

"Dave Peterson" wrote:

First, unprotect the worksheet.
then format the column getting the time/date the way you want.

Then protect the worksheet once again.

And remove the formatting from the code.

Changing the format of a cell on a protected sheet could be causing your
trouble.

If that doesn't work, post the code you used and indicate the line that fails.

Richard R wrote:

I used the McGimpsey code, and it worked, until I protected the sheet, then
it quit working entirely. I unprotected the 2 columns in the formula, but it
made no difference. Is there a trick to using it in a protected sheet?

Richard

"Dave Peterson" wrote:

J.E. McGimpsey shows a way to put a time stamp on the same row when something
changes:

http://www.mcgimpsey.com/excel/timestamp.html

Qlychap wrote:

Hi all,

I am using office 2003; I am trying to fix up a automated time tracking
system.

I like to find when the particular field is updated in the excel and
update the same in the adjacent row. Let me give you a example

Field A5 will be filled as Yes / No I want the A6 to be filled with the
date and time of the field A5 got updated

I tried with this formula: *@ A6 *I typed -=
IF(ISBLANK(A5)=FALSE,NOW()," ")

but the problem is this when ever I save the excel the A6 is getting
updated with the current time.

With regards
Pradeep

--
Qlychap
------------------------------------------------------------------------
Qlychap's Profile: http://www.excelforum.com/member.php...o&userid=35253
View this thread: http://www.excelforum.com/showthread...hreadid=550268

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson