How to count days, stop counting, then continue counting from where it left off

WillH

Hi,

I am working on a sheet to track parts that are waiting on different departments, such as Engineering, Debug, Materials etc.

I want to have a drop down box or separate tick boxes if a drop down wouldn't work that would start counting days the part has been on that department.

For example, Part 1 is selected as Materials, and it starts counting number of days. Then when it changes department say to Engineering then it stops counting Material days and starts counting Engineering days. The part could then go back to Materials and the number of days need to continue counting from where it got to the first time. So if it gets to 5 days then changes to Engineering then it would stop counting at 5 for Materials. Then if it goes back to Materials for 4 more days then the count would get to 9.

What would be the best way to make this work?

Thanks in advance!

Will

MichaelTCA

Hello @WillH

The only way I can see this working is if you copy each row over to an archive when a change to the part is made. There's no way to turn off a function and start it back up again.

You can use functions that reference the sheet that the rows are being copied to, to develop aggregate data.

Or in a report, you can summarize the values, like days, and group it by the part.

WillH

@MichaelTCA Thank you for your quick reply!

I was having a play around using helper columns and automations to clear tick boxes when another is selected and clear the start date for that department. What I was trying to work out is how to add the number of days on to the total. Do you think there could be a way of doing it this way?

If all else fails then I will go the route that you suggested, just wanted to try and avoid that if possible and contain it in the one sheet.

Thanks

MichaelTCA

@WillH

Without some sort of "screenshot" (I guess you could call it that), there wouldn't be a way to add it to the total.

When there is a stopping point or transition, the cell can hold the same value, but as soon as it starts counting again, it will be reset to how the function is written.

Unfortunately adding a value to itself without being a separate object is considered a circular reference which causes errors in the software.