Count time spent by making each step activity?

[Robys]

Hello to All, I'm writing from Italy;
This is my first post on this forum, using Excel since long time but not expert on VBA..<o></o>
This is my issue:<o></o>
During follow up of my team activity job, I use one list of problem we are working on, day by day;<o></o>
Each row contains one single problem.<o></o>
Each column contains data (many) related to the problem (problem name, when received, who is in charge etc.)<o></o>
<o></o>
One of the column contains “step” of problem (for example “waiting sample”, “checking sample”, “making test”, etc).<o></o>
We have with team weekly followup of activity, updating the status of each problem according to real progress.<o></o>
<o></o>
What I would like to have (also to investigate bottleneck at the end of activity) is a way to “count” days we spent in any step of activity (please consider that sometimes we change status more than one time on the single “step”, for ex we can have “checking sample” à “making test” à “checking sample”…<o></o>
<o></o>
So, I’m considering to have inside my sheet, some additional column with label to represent each step (for ex: waiting sample,checking sample,making investigation,making test …) and in the correspondant row of each problem it should be the number of days spent for each step<o></o>
<o></o>
How to do?<o></o>
Basically I think it should be some function registering the date when the status of "step cell" has been modified (counting how many days past since previous modification)and putting result (number of days) in the correspondent cell of each step time spent, adding to the previous number of days if different from 0…<o></o>
<o></o>
Easy to say…but how to realize?<o></o>

 

[oldbrewer]

Re: How to count time spent by making each step activity?

	received	in charge	sample rec'd	checking sample	testing	analysing	final report	STATUS	total days
problem1	01/08/2017	bill	04/08/2017	05/08/2017	06/08/2017	08/08/2017		analysing
problem2	02/08/2017	fred	03/08/2017	03/08/2017	04/08/2017	05/08/2017	06/08/2017	COMPLETE	4
problem3	02/08/2017	tom	02/08/2017	03/08/2017	05/08/2017			testing
problem4	02/08/2017	sid	03/08/2017	10/08/2017				checking sample
problem5	03/08/2017	dave	05/08/2017					sample rec'd
				formula of I2 (analysing)
				=IF(H2="",OFFSET($A2,-ROW()+1,MATCH(LARGE($B2:$G2,1),$B2:$G2,0)),"COMPLETE")
				if there is a date in column H (final report)
				the status automatically shows complete
				and when col I = complete
				the total days is automatically calculated
				if there is a target date for each stage
				easy to conditionally format red or green to highlight delays
				maybe only a target date for completion ???

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Robys]

Re: How to count time spent by making each step activity?

Thank you Oldbrewer for your support.
Your way I understood, but I'd like to use a different way, and my point is not related to target to finish (of course we have a target, but my today issue is not related to that..)
Due to in our activity we have (for example) 6 (fixed) step, but each step can be ongoing several time, I'd like to have one cell only for each problem that must be updated (like a flag to set) when the related activity is moving to another step (between the 6 total step).
And each time this cell is changed as condition (between the 6 possible step) automatically the correspondent (same row) step cell will be updated with total number of days for that step.
Example (I just show 4 step of 6)

N. Resp. activity step ..... ...... Waiting part Checking part Testing part Making invest.
1 Bill checking part .... .... 13 5 0 0
2 Tom testing part .... .... 25 1 2 0
3 Jimmy waiting part ..... ..... 3 0 0 0
4 Robert making invest. ..... .... 1 1 6 3
5 ........ ......


As said some time we need to "touch" one activity step more than one time, so days counter should restart from the number of days already spent previously for the same step.
I hope enough clear, if need I will attach excel file example to show better
Thank you!

 

[oldbrewer]

Re: How to count time spent by making each step activity?

maybe you need a "step complete" column for each step, so even if you moved on to the next step, then had to go back to previous step the date would reflect the true date that a step was finished.....

 

[Robys]

Re: How to count time spent by making each step activity?

I hope with this example it will be more clear what I need

Foglio1

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	N.	Kind of problem	In charge to:	Coming from	Team step	Investigation started	Investigation closed	Waiting part days spent	Sample check days spent	Investigation days spent	Making test days spent	Step 5 days spent	Step 6 days spent
2	1	Broken wheel	Bill	Italy	Waiting part	24/09/2017		23	0	0	0	0	0
3	2	Pressure lost	Jeff	Norway	Sample check	21/09/2017		3	22	0	0	0	0
4	3	Rust on bumber	Ronald	UK	Waiting part	23/10/2017		3	0	0	0	0	0
5	4	Oil leakage	Nick	Sweden	Investigation	06/05/2017		27	3	68	25	0	0
6	5	Abnormal noise	Robert	France	Making test	28/08/2017		16	4	20	3	0	0
7	6	Paint peeling	John	US		08/01/2017	05/10/2017	36	15	40	53	25	25
8
9
10
11

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4