hi,

i have records in a column. the records are job works to be done. once
o job is done , i mark a "x" in the next column, indicating job
completed. i want the time of compeltion in the third column entered
automatically, once i make a mark in the second column. The mark need
not necessarily be a "x". it could be anything, say like a check box
also.

can it be done???

Thanks for your help..


---
Message posted from http://www.ExcelForum.com/

Rvik,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then Target.Offset(0, 1).Value = Now()
End Sub

Rob


"rvik " wrote in message
...
hi,

i have records in a column. the records are job works to be done. once
o job is done , i mark a "x" in the next column, indicating job
completed. i want the time of compeltion in the third column entered
automatically, once i make a mark in the second column. The mark need
not necessarily be a "x". it could be anything, say like a check box
also.

can it be done???

Thanks for your help..


---
Message posted from http://www.ExcelForum.com/

ROB,

where do i enter the code... how do i go about it?

--
Message posted from http://www.ExcelForum.com

It goes in the worksheet code module. Right-click on the sheet name tab,
select view Code from the menu, and copy the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rvik " wrote in message
...
ROB,

where do i enter the code... how do i go about it??


---
Message posted from http://www.ExcelForum.com/

bob,

it worked, but in column c where the time is supposed to be displaced,
the time is dispalced as #########.

i tried formatting the entire column but in vain. i have to format
each cell individually to display the time


---
Message posted from http://www.ExcelForum.com/

Just make tyhe column wider.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rvik " wrote in message
...
bob,

it worked, but in column c where the time is supposed to be displaced,
the time is dispalced as #########.

i tried formatting the entire column but in vain. i have to format
each cell individually to display the time


---
Message posted from http://www.ExcelForum.com/

bob,

i want only the time to be displace... but widening the column, th
date & time is displaye

--
Message posted from http://www.ExcelForum.com

Sorry, didn't read the OP

Replace Rob's code with this modified version

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(0, 1).Value = Now - Date
Target.Offset(0, 1).NumberFormat = "[h]:mm:ss"
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rvik " wrote in message
...
bob,

i want only the time to be displace... but widening the column, the
date & time is displayed


---
Message posted from http://www.ExcelForum.com/

bob,

pooohhhh.... at last i got ir right... thanks a lot bobby...


can u pls look into my other thread "copying data...."

anyway thanks a lot


---
Message posted from http://www.ExcelForum.com/

Thanks Bob - missed that one :)
Now - Date is a handy trick.


"Bob Phillips" wrote in message
...
Sorry, didn't read the OP

Replace Rob's code with this modified version

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(0, 1).Value = Now - Date
Target.Offset(0, 1).NumberFormat = "[h]:mm:ss"
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rvik " wrote in message
...
bob,

i want only the time to be displace... but widening the column, the
date & time is displayed


---
Message posted from http://www.ExcelForum.com/

Or just
Target.Offset(0, 1).Value = Time



Rob van Gelder wrote:

Thanks Bob - missed that one :)
Now - Date is a handy trick.

"Bob Phillips" wrote in message
...
Sorry, didn't read the OP

Replace Rob's code with this modified version

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(0, 1).Value = Now - Date
Target.Offset(0, 1).NumberFormat = "[h]:mm:ss"
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rvik " wrote in message
...
bob,

i want only the time to be displace... but widening the column, the
date & time is displayed


---
Message posted from http://www.ExcelForum.com/




--

Dave Peterson

Dave,

True... It's Friday here. Brain winding down :)

Rob


"Dave Peterson" wrote in message
...
Or just
Target.Offset(0, 1).Value = Time



Rob van Gelder wrote:

Thanks Bob - missed that one :)
Now - Date is a handy trick.

"Bob Phillips" wrote in message
...
Sorry, didn't read the OP

Replace Rob's code with this modified version

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(0, 1).Value = Now - Date
Target.Offset(0, 1).NumberFormat = "[h]:mm:ss"
End If
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"rvik " wrote in message
...
bob,

i want only the time to be displace... but widening the column, the
date & time is displayed


---
Message posted from http://www.ExcelForum.com/




--

Dave Peterson

i want to hide the code and also i would like to have the thrid colum
protected, so that others don't change the time.

If i share thebook, others must beb able to type in only in the secon
column. the thrid column displaying the time , should not be editable

thank

--
Message posted from http://www.ExcelForum.com

i would like to share this xl sheet on a network and let others also t
mark x in the sheet to record a time but this time should be standar
i.e. it should pick up the time from one central location..say th
server

any solution

--
Message posted from http://www.ExcelForum.com