Date functions
 

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.

Date functions
 

To enter today's date into a cell, use Control + semi-colon.
--
David Biddulph

"matchwalk" wrote in message
...
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.

Date functions
 

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.





"Gary''s Student" wrote:

First entered I guess means if the data changes leave the first date in.
Here is an example for data entered in column A. Dates are automatically
entered in column B:


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A:A"), Target) Is Nothing Then
Exit Sub
End If
If IsEmpty(Target.Offset(0, 1)) Then
Application.EnableEvents = False
Target.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End If
End Sub


This is worksheet code and does not go in a standard module.
--
Gary''s Student
gsnu200712

Date functions
 

Fantastic - thank you VERY much!




"Gary''s Student" wrote:

This version will work with any cell, not just cells in column A
This version works with mutiple cells, so copy/paste will record the date
for an entire block of cells:


Private Sub Worksheet_Change(ByVal Target As Range)
For Each r In Target
If IsEmpty(r.Offset(0, 1)) Then
Application.EnableEvents = False
r.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End If
Next
End Sub

--
Gary's Student
gsnu200712

Date functions
 

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...




"David Biddulph" wrote:

To enter today's date into a cell, use Control + semi-colon.
--
David Biddulph

"matchwalk" wrote in message
...
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.

Date functions
 

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




--
Gary''s Student
gsnu200712


"matchwalk" wrote:

Now having had a chance to look at this a little closer, I am coming to the
conclusion it is not what I was hoping it might be. It's a macro and
therefore not something I can run from within a cell (as I understand the
help documentation).
I was looking for a custom function and I know next to nothing about VBA, so
am unsure how to make this into one, if it can be made into one at all. I've
tried changing 'Sub' into 'Function' but it does do it for me.
The good news is that you have given me a much better idea as to how to
start. For instance, providing I don't try to use a range, the guts of the
macro work very well in a cell as an IF() statement, e.g. IF (C2<"", NOW(),
""). I now just need to understand how to make it work with a range of
target cell values. I've tried IF (C2:H2<"", NOW(), "") and also IF (C2<""
AND B2<"" AND... etc, NOW(), "")but this doesn't work - obvious to you,
perhaps, why, but not yet to me...



"Gary''s Student" wrote:

This version will work with any cell, not just cells in column A
This version works with mutiple cells, so copy/paste will record the date
for an entire block of cells:


Private Sub Worksheet_Change(ByVal Target As Range)
For Each r In Target
If IsEmpty(r.Offset(0, 1)) Then
Application.EnableEvents = False
r.Offset(0, 1).Value = Now()
Application.EnableEvents = True
End If
Next
End Sub

--
Gary's Student
gsnu200712