Quote:
Originally Posted by steve.
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
|
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:
Code:
=IF(E21,IF(F2="",NOW(),F2),"")
...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.