Current Time Capture When Formula Result Turns to 0

[reachharry]

Hi,

I have written a vlookup formula in B column when Vlookup returns 0 I need a current time to get captured in C column. Once the time is captured, it should not change.
Problem i am facing is, when other vlookup formula results to 0, earlier time also gets refreshed.
Appreciate help in this regard...

[LukeGilfoyle]

Through a formula, this isn't possible using the NOW() or TODAY() functions as they will constantly update when the workbook re-calculates.

Have you considered using the Ctrl+: and Ctrl+; options to manually insert a date/time?

Thanks

[reachharry]

Hi,

I want the time to get captured automatically whenever the formula result goes to 0

[LukeGilfoyle]

You will have to do this through VBA.

I'm sure someone will come along and will be able to assist you further in writing VBA code to check if the cell = 0 and then to date/time stamp the cell next to it.

Thanks

[MrShorty]

I don't know that it "requires" VBA, though some might find it easier to do. I expect that some variation of this formula method of inserting time stamps should work for "timestamping" when a VLOOKUP() returns 0: http://chandoo.org/wp/2009/01/08/tim...-formula-help/

[reachharry]

Appreciate the help..I am trying to get a solution which can work for any volume data. My data volume easily crosses 95000 rows every day easily, in my opinion circular reference would not be optimum solution.
I tried macro code for it using worksheet change event, it gets the time stamp only when the value 0 is manually entered. It is not getting the time stamp when the formula calculates..is there any perfect solution this scenario.
I am trying to use the worksheet calculate event, not sure what code can make it to work..

[MrShorty]

I am not sure why 95000 rows should be a concern. If you are concerned that this kind of circular reference (that should only need one iteration per calculate event) will be "slow" to calculate, I have a hard time believing that this would be significantly slower than a VBA solution, and could easily be faster (since it avoids the overhead that seems unavoidable every time VBA and the spreadsheet need to interact). If that is your concern, I would point you to this situation (https://www.excelforum.com/excel-pro...-possible.html ) where the OP compared my simple circular reference to some VBA that he commissioned and he/she indicated that the simple circular reference was faster than his/her VBA solution.

In any case, it is up to you to decide how you want to approach it. If you want to pursue the calculate event procedure, it might help to be clear on exactly what trouble you are having. Is it failing to call the procedure? What statements have you tried? As with a lot of this kind of programming, probably the first thing I would do would be to add a stop statement to the top of the procedure so that it will enter debug mode as soon as the procedure is called. That would tell me if the procedure is being called correctly or not, and allow me to investigate the variables and statements to see where it is going wrong.