Forum Discussion
Using multiple NOW formulas in a spreadsheet
I have four cells on a spreadsheet. A1 and A2, both are formatted as numbers. B1 and B2 are using a conditional formula to add a date/time stamp when a value is changed in A1 or A2. For example, the formula in B1 is =if(a1<>0,now(),""). I have similar formula in B2 =if(a2<>0,now(),""). The problem I am having is if I change a value in A1 or A2, the date stamp is not only updated in B1 but also in B2. My intent is to have A1 and B1 treated seperately, just as A2 and B2 should be. This is a simplified example and I really need many date stamps for numerous values. I suppose a macro could be created to accomplish what I am trying to do, but I would prefer to avoid that route. Any suggestions?
comet64 Hans' recommendation applies if you are seeking to capture the first time a non-zero entry is made. If you need to capture the most recent time the entry was changed to a non-zero number, you would probably need to use VBA code in the Worksheet_Change event handler (~a macro).
The reason either of those techniques is needed is because Excel recalculates formulas many times, including each time a workbook is opened. More info
You need a trick for this - see Circular References Trick to Automatically Insert Date and Timestamp in Excel
