Current date and time when different cell = X

[Workaholic]

I'm trying to track manfacturing productivity between day and swing shifts.
I was hoping there was a function that would give the current date and time
WHEN a cell = x which will designate a task is complete. I've got a list of
tasks that are performed and as I mark them off as complete with an "x" I
would like a different column to display the date and time that each one is
checked off instead of manually entering this as things are completed. Help
anyone?

Thanks in advance...

 

[David McRitchie]

You would want to use an event macro
http://www.mvps.org/dmcritchie/excel/event.htm

 

[MartinW]

Hi workaholic,

As David said, to automate the procedure you would need
a macro, however, there is keyboard shortcuts that can
do this.

Ctrl+; will input the date
Ctrl+Shift+; will input the time
Ctrl+; Space Ctrl+Shift+; will input the date and time

They aren't volatile so the time and date will stick.

HTH
Martin

 

[Workaholic]

Thanks. I'm new to macros and VB. I found the macro to enter just the date.
That worked. I just need to find one that does both date and time. The
other question is, how do I specify which cells this applies to. The macro I
find was general. I tested it in a blank worksheet but I actually want to
use it on a worksheet with other data that won't be effected. Thanks again.
Please advise. Here's the macro. How do I specify the cells?

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
If IsEmpty(Target(1)) Then Exit Sub '-- also rejects if entire row cleared
If Target.row = 1 Then Exit Sub
If IsEmpty(Target.Offset(0, 1)) Then
Target.Offset(0, 1) = Date
Target.Offset(0, 1).NumberFormat = "yyyy-mm-dd"
End If
End Sub