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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Workaholic" <[email protected]> wrote in message news:[email protected]...
> 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...

[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



"MartinW" wrote:

> 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
>
>
>