Multiple Time Stamps depending on choice in Drop Down List Choice

Hi everyone,

My very first post here but I've found the help in here to be so good I just knew where to turn now that I can't seem to solve it on my own.

I'm trying to put together a sheet that tracks task completion. Basically, every row represents a task. In column L I have created a drop down where the CR agent (this is for a callcenter) chooses the status of the task he/she is working on (the options being Standing, In Process, Pending, Closed). In the following four columns (M, N, O and P) I would like to see the time and date (i.e. command NOW) for when each of these stages where started.

So, if an agent chooses "Standing" in the dropdown (column L), then a timestamp should be added to column M. When the agent later updates the dropdown (column L again) to status "In Process", then that adds a new timestamp in column N (please note that none of the timestamps should ever change).

In similar fashion, changing the dropdown status (column L still) to "Pending" should add a timestamp in column O and changing it to "Closed" should add a timestamp in column P.

I should be able to figure this out on my own, but I'm just giving up Anyone help please?

Thanks!!

insayah,

Welcome to the Excel Forum.

You can use the Worksheet_Change Event for the 4 columns where the data validation is located.

Please attach your workbook/worksheet, and in a few rows in columns M, N, O and P, manually enter the date and time format that you would like to see.

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

insayah,

Detach/open workbook CR_workbook - insayah - EF767930 - SDG13.xls.


On worksheets 1.1 thru 3.1, make changes/choices in range J11:J20, and range L11:L20



In your actual working workbook(s), copy the below code into each of the worksheets where the data validation is set in colukmn J and L beginning in row 11.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 03/12/2011
' http://www.excelforum.com/excel-programming/767930-multiple-time-stamps-depending-on-choice-in-drop-down-list-choice.html
If Intersect(Target, Range("J:J", "L:L")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
If Target.Row < 11 Then Exit Sub
If Target.Count > 1 Then Exit Sub
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  If Target.Column = 10 Then
    Target.Offset(, -7) = Format(Now, "mmmm, dd, yyyy hh:mm AM/PM")
  End If
  If Target.Column = 12 Then
    Select Case Target.Value
      Case "Standing"     'Column M
        Target.Offset(, 1) = Format(Now, "mmmm, dd, yyyy hh:mm AM/PM")
      Case "In Process"   'Column N
        Target.Offset(, 2) = Format(Now, "mmmm, dd, yyyy hh:mm AM/PM")
      Case "Pending"      'Column O
        Target.Offset(, 3) = Format(Now, "mmmm, dd, yyyy hh:mm AM/PM")
      Case "Closed"       'Column P
        Target.Offset(, 4) = Format(Now, "mmmm, dd, yyyy hh:mm AM/PM")
    End Select
  End If
  .ScreenUpdating = True
  .EnableEvents = True
End With
End Sub

Then make data valadation selections in columns J and L, beginning in row 11.

insayah,

Detach/open workbook CR_Dashboard - insayah - EF767930 - SDG13.xls, and make changes to the data validation cells in columns M and O beginning in row 12.


You have changed the original columns from J and L to M and O.

Original columns J and L:
Column C is the date when the person is assigned in column J:
column J is the same as Target.Column = 10

Column M,N,O,P is the date when the status changes in column L:
column L is the same as Target.Column = 12


New columns M and O:
Column F is the date when the person is assigned in column M:
column M is the same as Target.Column = 13

Column P, Q, R,S is the date when the status changes in column O:
column O is the same as Target.Column = 15

Hi Stanley,

Can you please help me in programming on the similar lines where I want Start Time to be auto captured whenever the person selects drop-down value in Status column e g. Production, Training, etc.?

Please note i only want to capture "Start time" in h:mm AM/PM format.

Also is there any way we can get end time on a click once the transaction is completed?

Don't know i am not getting option to attach any attachment.

Administrative Note:

Welcome to the forum.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread