Auto date insert when cell is occupied

Hi

I know that there are threads on this site trying to deal with this issue, and till now i am still unable to get it to work. I have been told locally that it can't be done, but reading various other threads simular to what i am trying to do i think there is or must be 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(E28>0,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!!)

Hello & Welcome to the Board,

You'll need a worksheet change event macro for this.

Please Login or Register  to view this content.

You are right about the formula. The Now() function gives you the current date/time...so, every time you either close and reopen the workbook or hit F9 to recalcualte the worksheet the Now() function refires giving you the current data/time.

This is a worksheet module so it needs to be placed in the worksheet code. Right click on the sheet and select view code. Paste the macro above on the right side of the screen. Alt + Q will close the VBE and return you to Excel.

Go to the sheet and enter something in E. The cell just to the right in F will now get the date which is a hardcoded so will not change when you open the workbook or hit F9.

Here is some additional reading about where to place code.

Where to paste code

Hi Steve,

You're welcome...glad it is going to work for you.

If you are satisfied with the answer provided, please don’t forget to mark the thread as solved.

How to mark a thread Solved

Go to the first post
Select
-- Edit
-- Go Advanced
-- Below the word Title you will see a dropdown with the word No prefix
-- Update to Solved and then select Save