Automatically changing the date when a cell changes value

**Tristanfrontline** · 07-05-2014, 09:43 AM

Hi
I understand the formula NOW() will allow me to have the date and time automatically change when a cell is altered anywhere on my worksheet. What I want to do is create a formula where if a specific cell is changed the date and time will automatically change only if that specific cell is altered. For example if the value in c2 is altered then the date and time is automatically changed to reflect that but if d2 was altered nothing would happen. I think an IF function would work here but if it does what would I need to write as the logical test? =if(logical test(change in value),Value if true (now()),value if false(No change)). Am I heading in the right direction or am I way off? I understand there is VBA codes for this I just thought I would try it without VBA.
Any help would be greatly appreciated

**Glenn Kennedy** · 07-05-2014, 09:54 AM

Yes, this can be done using the attached sheet, as an example.

**Glenn Kennedy** · 07-05-2014, 09:56 AM

Sorry, I forgot to add - this is a self-referencing formula (a.k.a. circular reference). You need to enable iterative calculations by hitting (in E 2007...) : Big Excel Button, Excel Options, Formulas & then clicking the enable iterative calculations box.

The example is formatted for date, but you can re-format for date:time from custom formatting if you wish.

**Tristanfrontline** · 07-05-2014, 10:37 AM

Thanks for that it works well except if I try to overwrite a previous entry then it wont update automatically. It only works if it is a fresh entry

**Glenn Kennedy** · 07-05-2014, 11:00 AM

Dohh. Try agai. Most people want a totally static datestamp. So try this. There's a bit of code that updates the date:time in B2 when A2 is altered. Is this more like what you want?

**Tristanfrontline** · 07-05-2014, 11:09 AM

Thanks heaps for that. That's what I was looking for. So I am guessing this can't be done without VBA?

**Glenn Kennedy** · 07-05-2014, 11:15 AM

Nope. I'm totally cr@p at VB (seriously totally cr@p, to be truthful). So, if I can get it to work - anyone can!! Just copy the code as needed.

If that's it can you mark the thread as solved. it would also be nice if you clicked the add Reputation button at the foot of this post.

**Tristanfrontline** · 07-05-2014, 11:17 AM

Im terrible at VBA myself but I was hoping there would be a formula that would do this. You have been very helpful at least and I now have answer to my question

**martindwilson** · 07-05-2014, 11:21 AM

it will update if you clear contents then re-enter you cant overwrite and just enter

**Glenn Kennedy** · 07-05-2014, 11:29 AM

Originally Posted by martindwilson

it will update if you clear contents then re-enter you cant overwrite and just enter

It works for me... if I type anything into A2, the datestamp in B2 updates instantly. No need to clear it. (or was Martin referring to my first attempt??)

Yes - he was. And he's right. I never noticed that before - on my first effort (post #2), if you delete the value in col A first, before entering something else, the datestamp updates.

Automatically changing the date when a cell changes value

LinkBack

Thread Tools

Rate This Thread

Display

Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Re: Automatically changing the date when a cell changes value

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2007 : sorting date and changing value automatically

automatically changing date

Automatically Changing a Value on a Set Date?

Automatically changing cell value on a given date

Changing Cell formats to date fields automatically

Bookmarks

Bookmarks

Posting Permissions