Excel Date Entered

I have a spreadsheet in excel that contains columns such as Name, DOB, form no etc, and I want to add a column that will auto enter the date that the information was entered. I tried adding a column which uses the TODAY() function when something is added to the column to the left but everyday it updates the TODAY() to current day so that won't work. Can anybody think of a way to stop it updating or another way to put the "entered date" on?

try the now() command, this will not update automatically.

This is the help text from excel

Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date.
Syntax
NOW( )



Remarks

• Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

• Numbers to the right of the decimal point in the serial number represent the time; numbers to the left represent the date. For example, the serial number .5 represents the time 12:00 noon.
• The NOW function changes only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.

Hi eggyuk,

I have managed to find a solution for you. This is using Office 2007. The first image shows the headings I have used for my example.

The next 2 images show how to format columns A and B to show dates. Right click on the column heading and select 'Format Cells...'.

In the 'Number' tab, under 'Category:' select 'Date'. In the panel under 'Type:', select how you want the date to appear and then click 'OK'

The formula used in column A refers back to itself, so these next 2 images show how to enable this. Click on the Office button and then click 'Excel Options'.

Select the 'Formulas' page and then click to put a tick in the checkbox 'Enable iterative calculation' and then click 'OK'.

The next 2 images show the formulas for columns A and B respectively.


The next sequence of images shows this in action. The data I used in all cases was simply the word 'test', but of course you can change this to what you like.
Firstly, Data 1 with today's date.

Secondly, Data 2 with tomorrow's date.

Thirdly, Data 3 with today's date.

Note how in each case column A remains constant whilst column B changes to reflect the current date.
Finally, deleting all data from columns C, D, and E. Notice how columns A and B clear as well.

Hope this helps. At the moment, there is still a bug in the formula for column B in that it still changes for all rows if you create, delete, edit or modify any of the data in any row. I am looking at this so that only the current row that you are working on is affected. When I have found the solution, I shall post it here.

Hi eggyuk,

Unfortunately, I have not been able to come up with a solution for this bug with the formula for column B in my example (perhaps the developers of Excel will come up with a function that can detect which row and column the cursor is on, which seems to be the sticking point).
I therefore propose that you delete column B in my example and, instead, modify the formula in Column A to read as follows (increase or decrease the number of terms in the first nested () to take account of the number of columns you are dealing with):
Remember that column A will only show the date that the FIRST bit of information was entered in any of the associated columns, and that it will stay showing that date even if you subsequently amend any of the data on THAT row irrespective of the current date. As before, if you delete all the data in the row then column A will automatically clear on that row.

hi guys. Thanks for your replies. Unfortunately I've been majorly busy at work recently so I haven't been able to try them out yet. As soon as I can make time to "play" I'll let u know how it went. Thanks again

Ok I've had a play around and it works on my spreadsheet so far. It is correctly entering todays date when i put something in another cell. I will check that it doesnt change the date when i enter somethnig tomorrow and report back.

Hi eggyuk,

To check this out, temporarily change the formula in column A as follows:
In addition, change the cell format on column A from the date format to 'General'. In the rightmost column (the first column that you don't use), ensure that the format is set to 'General' and simply enter the formula:
When you amend any of your data, you will find that the value in column A will stay constant, but that values in this column will change.
Once you are satisfied with the functioning of this, you can revert column A back to its original state (formula and column format) and delete the extra column.
Incidentally, I used the 'NOW()' function as opposed to the 'TODAY()' function when I was originally working out my original solution as it was much easier. After I had tested it and confirmed that it was working, I then substituted 'TODAY()' into the formula in place of 'NOW()'. I also changed the format of the column so that the information would be displayed correctly.

OK. I've managed to test it out and it works perfectly. Hopefully I'll get a promotion/pay rise as a result of my new improved spreadsheet that solved a problem that didnt exist and I just made it cos I was bored at work one day. Somehow I dont think i will get that raise :huh:

Thanks for the help Dwarf. Greatly appreciated.