How to add a clock or timer in Excel to measure how long until a field has changed

Occasional Contributor

Hello,

 

I have a spreadsheet with a "status" drop down and I would like to see if there is a way to be able to track how long it has been in certain statuses.  For example, when a record is first added it would have a status of "Unassigned" and I want to track how long it was in this status before it went to a status of "Under Review" and then how long it was in this status before it went to a closed status.

 

I hope what I am trying to do is clear and appreciate any help.

 

Thank you

8 Replies
Of course this can be done. Would a macro solutions be OK?

Hi @PascalKTeam,

 

I think a macro solution would be good.  I have dabbled in macros and was looking around online initially how to do this, but did not find something that was the similar to what I need to do.  

 

Are you able to give me some suggestions on how to do this with a macro?

 

Thank you!

You can set up 2 data tab -
- 1st data tab is your Current Day data set, and
- 2nd data tab is Previous Day data set.

Every day when you run your report -
1. Replace data set in Prev. Day with data in Current day,
2. Update Current Day data set, and
3. Compare the status of same record between current day and previous day. If status of a record has changed, record the date of current day.

Macro would be very helpful when moving and updating data sets.

Hi @szub_2 

 

The attached file shows the principle of how this can be done. just play around with it by changing the status dropdown in the yellow cells. I hope this helps

Hi @PascalKTeam

Thank you for file.  I think this should work for what I am trying to do. Did you use a macro and Visual Basic?  I am trying to replicate and I am not getting the same results.  Both dates for Under Review and Unassigned change when I update the status. 

 

Will you please give me some detail of what you did to get the result?

 

Thank you!

Hi @szub_2 

 

Yes, I used VBA. You can look at the code by pressing Alt+F11.

 

For me it works fine, see attached video

@PascalKTeam ,

Unfortunately, I am not able to view your video. I could get your file to work, but when I tried to recreate myself, it was not working.

 

Thanks - szub

Well now I don't know what exactly the issue is so I can't really help. Do you want to send me your file so I can integrate the VBA code directly?