auto date update when cell is occupied
 

Hi

I know that there are threads on thsi site trying to deal with thsi issue, and till now i am still unable to get it to work. I have been told locally that it can't bedone, but readind various threads simular to what i am tryin gto do i think there is a solution.
This is what i am trying to do( i am no excel boffin by any means so please keep it simple)
Cells A = invoice no
Cells B = location
Cells C = Payment amount due (£)
Cells D = Invoice sent out (Y/N)
Cells E = Invoice Paid in (amount £0.00)
Cells F = Date Paid

So what i am trying to sort out is when i enter the data into Cell E i want the date to automatically placed / auto enter into Cell F. This have done by using the following formula:
=IF(E280,NOW()," "). but by using this formula it changes every time when i load the spread sheet on a different date
i.e.
entered the amount in Cell E, say on the 20th May 2011 it then automatically entres the date of that day using the formula as above, and saved it the sheet.

Reopen it again say on the 25th May 2011 the date changes from the 20th to the 25th, which is not what i want. All i want is for the date to remain the same as when i entered the amaount in Cells E on the 20th, this then shows me the date of when it was either entered or paid.

Please can anybody advise me on how to sort this out.

Look forward to your assistance.

Please keep it simple (idiots guide would great!!)

Hi again.

Is there anybody out there that can help with this issue, i have seen that there are over 100 views but no assistance in helping this this problem!!

please can somebody respond!!

You've stumbled upon a bit of a tricky one here - hence the delay in getting a response. There are a few discussions on this on the Internet (search for "Excel Timestamp") but in short, you need to purposely create a circular reference.

The first thing you need to do is to enable the Iterative Calculation capability. This stops the circular reference warning and allows a formula to refer to itself. To do this in Excel 2007 (File/Options I presume in Excel 2003) select the Window button (round button in the top left corner) and click Excel Options. Under the Formulas tab, tick the Enable Interative Formulas option.

Now you can use the formula:
...and paste this down column F.

Whilst this formula works fine, I find that it can revert back to zero (e.g. "00-Jan-00 00:00:00") if you edit the formula or do anything silly with it. So periodically it would be a safe move to Copy and Paste Values of any dates that come up using this formula just to protect any data from being lost.