tracking history in how long a cell was at with specific text

Occasional Visitor

Hello,

Thank you in advance in helping me.

 

I have a spreadsheet that I want to record how long the cell was at each stage, for example;

One cell will have a drop down where the user will select what stage the work order is at.

  • booked in
  • parts arrived
  • work in progress
  • Completed

Is there a way to track how long it was marked at each stage?

 

I can timestamp it with current time when updated, but have not been able to work out how to timestamp and retain that when the status changes.

 

I will be comparing this in another cell for how long the work was booked in for compared how long it was actually in progress

 

 

1 Reply

@bilbym 

Hi Melissa, I tried to find a solution with IFS() functions. No VBA. Because the formula refers to its own cell, the entry is retained once it has been created. Have a look at my example to see if you can do something with it.

Since you didn't mention what unit the difference between the different status entries should have. I have now used hours:minutes:seconds. If you want to have days, that can also be set.

 

I have left the raw data in row 2. That is, the time stamp when the changeover took place. In the other rows, the difference to the previous status.