Closed Thread
Results 1 to 9 of 9

Multiple Time Stamps depending on choice in Drop Down List Choice

  1. #1
    Registered User
    Join Date
    03-11-2011
    Location
    Shanghai, China
    MS-Off Ver
    Excel 2010
    Posts
    4

    Red face 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!!
    Attached Files Attached Files
    Last edited by insayah; 03-11-2011 at 11:33 PM.

  2. #2
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Multiple Time Stamps depending on choice in Drop Down List Choice

    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.
    Have a great day,
    Stan

    Windows 10, Excel 2007, on a PC.

    If you are satisfied with the solution(s) provided, please mark your thread as Solved by clicking EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

  3. #3
    Registered User
    Join Date
    03-11-2011
    Location
    Shanghai, China
    MS-Off Ver
    Excel 2010
    Posts
    4

    Smile Re: Multiple Time Stamps depending on choice in Drop Down List Choice

    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

  4. #4
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Multiple Time Stamps depending on choice in Drop Down List Choice

    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.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-11-2011
    Location
    Shanghai, China
    MS-Off Ver
    Excel 2010
    Posts
    4

    Wink Re: Multiple Time Stamps depending on choice in Drop Down List Choice

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

  6. #6
    Forum Expert
    Join Date
    10-10-2008
    Location
    Northeast Pennsylvania, USA
    MS-Off Ver
    Excel 2007
    Posts
    2,387

    Re: Multiple Time Stamps depending on choice in Drop Down List Choice

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

  7. #7
    Registered User
    Join Date
    03-11-2011
    Location
    Shanghai, China
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Multiple Time Stamps depending on choice in Drop Down List Choice

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

    All the best
    /elliot

  8. #8
    Registered User
    Join Date
    01-25-2022
    Location
    Pune, India
    MS-Off Ver
    2016
    Posts
    1

    Re: Multiple Time Stamps depending on choice in Drop Down List Choice

    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.
    Last edited by Ashu1991; 01-25-2022 at 09:24 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,333

    Re: Multiple Time Stamps depending on choice in Drop Down List Choice

    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
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1