Excel Fox
03-29-2013, 01:50 AM
A lot of times we have had to track completion status of a particular activity, and we wanted to stop the timer on a particular date or time, after marking that activity as complete. We basically want to 'freeze' time, so to speak. A little search will give you a lot of solutions, but all of them are VBA based. I've hardly come across a formula based solution, so I thought I'll give it a try.
For this to work, you'll need to enable iterative calculation. Once you've done that, it all comes down to a few simple if conditions.
So let's say mark your activity completion status in column A
So in A2, you have a validation that allows two values, either Complete or In Progress.
Now, in B2, use =IF($A2="In Progress","",IF(B2="",NOW(),B2))
Now, toggle between your Complete/In Progress status. You'll see that when the status is In Progress, cell B2 will be blank (well, technically speaking, it won't be blank, as it'll have "". And ISBLANK("") returns FALSE ;)).
Now when you change the value in A2 to Complete, the value in B2 will show a timer value, ie, the result of the volatile NOW() function. You probably are thinking that the volatile nature of NOW() will kick in. You would also think that every time Excel workbook is saved, or every time there's a calculation in the sheet, the value will keep on changing, even if the status is NOT In Progress.
Well, that's the funny thing about Iterative Calculation. I'll let you look that up and figure out why the above mentioned formula WORKS. Don't forget to format your result cell as Date or Time (or both) depending on what exactly you need.
Disclaimer: If Iterative Calculation allows us to do the above, you got to assume that it'll stop something else work as you'd expect. I'll let you figure that one also yourself. Having said that, the above method works like a charm for this kind of specific requirement.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
For this to work, you'll need to enable iterative calculation. Once you've done that, it all comes down to a few simple if conditions.
So let's say mark your activity completion status in column A
So in A2, you have a validation that allows two values, either Complete or In Progress.
Now, in B2, use =IF($A2="In Progress","",IF(B2="",NOW(),B2))
Now, toggle between your Complete/In Progress status. You'll see that when the status is In Progress, cell B2 will be blank (well, technically speaking, it won't be blank, as it'll have "". And ISBLANK("") returns FALSE ;)).
Now when you change the value in A2 to Complete, the value in B2 will show a timer value, ie, the result of the volatile NOW() function. You probably are thinking that the volatile nature of NOW() will kick in. You would also think that every time Excel workbook is saved, or every time there's a calculation in the sheet, the value will keep on changing, even if the status is NOT In Progress.
Well, that's the funny thing about Iterative Calculation. I'll let you look that up and figure out why the above mentioned formula WORKS. Don't forget to format your result cell as Date or Time (or both) depending on what exactly you need.
Disclaimer: If Iterative Calculation allows us to do the above, you got to assume that it'll stop something else work as you'd expect. I'll let you figure that one also yourself. Having said that, the above method works like a charm for this kind of specific requirement.
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1 (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1)
https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw (https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)