Date functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'd like to be able to enter today's date into a cell in Excel. Doesn't
sound too hard, does it? Except, I'd like to be able to leave the date as it
was the next time I open the file and not have Excel update the damn thing to
the current date. It still doesn't sound too hard, does it? I want to be
able to detect data being entered into one cell and put the date the data was
first entered into another as some sort of audit trail without the date
changing every time I look at it. I cannot find a function to do this
(seemingly) simple task; am I missing something?

Thanks in advance for any help, just in case I am.
 
Your assumption is absolutely correct - I had in mind that, regardless of
whether the data changes, the date does not. Very many thanks for this -
I'll try it out this afternoon. One item remains; How do I get this to
reflect the fact that I need it to work on any cell, not just A or B, and how
could I arrange it so that copying a reference to the function down a column
of cells would automaticallt update the cell in which the date appears - or
does Excel take care of that automatically?

You raise another good point, though. How would the code change to reflect
a 'Last date edited' scenario?

BTW, I appreciate your very rapid response to the first question.
 
The whole point of this is to automate the entry of a date into a cell, not
have to enter it manually. This is the scenario...
I have a risk register. When I commence input of a new risk, I'd like the
entry to be assigned an index number and have the date of creation entered
automatically in another cell. Say column A contains the index numbers and B
the dates of creation; I commence entering details of a risk into cells C-H.
As soon as non-empty cells are detected in any of the columns C to H
inclusive, the index number should be entered into A, incrementing from the
index in the previous row, and the current date should go into B. Neither of
these should change when I open the log on any other occasion. A real bonus
would be if one cell (column) could contain 'last date this entry (row) was
edited' but that might just be too much to ask...
 
You can do this with a custom UDF. But its a little tricky. The Macro is
easy:


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 
Back
Top