Multiple Time Stamps depending on choice in Drop Down List Choice

**insayah** · 03-11-2011, 08:57 AM

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

**stanleydgromjr** · 03-11-2011, 10:25 AM

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** · 03-11-2011, 11:52 PM

Hi there Stanley,

thanks for the warm welcome, and I really appreciate your input! I'm attaching my workbook for this to (hopefully) make a bit more sense.

What I'm trying to do is this (referring to my workbook uploaded as attachment to my initial post);

Basically, I have 3 problems;

1) Timestamping the time created (detailed as tabs 1.1 and 1.2)

In column J, there is a dropdown with all the CR agents names in it. When anyone is chosen in this dropdown, the case is officially started and the clock "starts ticking" (we're evaluating the effectiveness of the staff by measuring average time per case). What I want should happen here is that a timestamp (command NOW) is added in column C on the same corresponding row.

2) Case Handling Status Timestamping (this is the problem I explained in my initial post). Ok, so this goes on in columns L through P. The dropdown in column L details the progress status for this specific case (expressed as Standing, In Process, Pending or Closed).

What I want should happen here (each tab explains the process sequentially from 2.1 to the end-state detailed in 2.5 for easier reference) is that when the dropdown is first set at "Standing", a time stamp (command NOW) should be added to column M (same row). This is detailed in steps 2.1 and 2.2.

When the status is later changed to "In Process", another time stamp should be added to column N (while still keeping the time stamp for "Standing" in column M) explained in tab 2.3.

When the status dropdown is again changed to "Pending", the corresponding timestamp should be added to column O (2.4). Lastly, when the dropdown is changed again to "Closed", the last and final time stamp should be added to column P (2.5).

The end result should be as expressed in 2.5 - 1 time stamp created in column C for when the case was started (i.e. when the handling agent was chosen in column J) and 4 different time stamps in columns M through P indicating when the case went through the different stages in the case handling process.

The end result I'm hoping to achieve here is expressed in sheet 3.1 where I can then calculate the response time for this specific case in B11 (i.e. the time duration between columns C and P) as well as an average response time overall in B10.

I'm supposed to have this ready by tmr and starting to feel a little stressed getting this together so any help or pointers in the right direction would be MUCH appreciated!

All the best

/elliot

**stanleydgromjr** · 03-12-2011, 07:42 PM

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

Please Login or Register  to view this content.

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

**insayah** · 03-13-2011, 11:15 AM

Hi there Stanley,

OMG, you have been so incredibly helpful in getting me through this, thank you for everything....!!!

I downloaded the workbook you attached with your last reply (the 3.1 tab worked just fine with what I wanted to do so I just took it from there). I made all the adjustments, data validation, formatting etc etc, and the file worked just fine. NOW, when I'm finally done, I got an error message on the Visual Basic and I have no idea how to debug it. Think you could spare a few minute more of your time and please just have a look at the code and see if you could get tab "09" to work again....?! (it's the VB coding that sets all the timestamps that for some reason just suddenly stopped working)

I would really appreciate it, thank you for all your help...! If you're ever in Shanghai, I'll buy you dinner...!!

All the best
/elliot

**stanleydgromjr** · 03-13-2011, 03:05 PM

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

**insayah** · 03-13-2011, 10:18 PM

Wow, and now it's all back to being awesome again - can't thank you enough Stanley, really appreciate it....!

All the best
/elliot

**Ashu1991** · 01-25-2022, 09:22 AM

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.

**AliGW** · 01-25-2022, 09:53 AM

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