Segregating NOW function

[jip126]

I'm making an accountability log where one of the functions uses the NOW function to auto detect the time that a selection was made. The problem I'm running in to is that every time any edit is made, every cell that uses the NOW function updates to the current time. This is making it impossible to capture the time where the edits were made to the specific cells that the function is meant for. Here is an example of the formula I'm working with:

=IF(H2=1,NOW(),0)

As it's written, as far as I understand it, the cell that this is built in to should update with the current time when an edit is made to cell H2. What's happening is any time any edit is made throughout the spreadsheet this, and every other cell with a version of this formula, updates to the current time.

Any ideas?

 

[dreid1011]

Welcome to the forum.

Your best bet is probably going to be VBA as NOW is volatile and changes anytime the worksheet recalculates. Otherwise, maybe add a new column that you can manually copy/paste values once you have the time. Though, you would have to copy immediately after getting the time as any other change to the sheet will re-calc the formula.

 

[jip126]

Thank you dreid1011. I was unfamiliary with VBA.... turns out there a whole dark underbelly of excel that I still have yet to learn. Thanks for pointing it out, but I feel like ignorance was definitely bliss, as I believe this is going to be occupying way too much of my time trying to figure it out.

Be well, and thanks again.

 

[dreid1011]

You're welcome. It shouldn't take too much to figure it out. That's what we're here for.

 

[Fluff]

Not sure if this is much help, but
Ctrl+shift+; will insert the current time
Ctrl+; will insert the current date