display date of today in cell X when number >0 in cell Y

[anneleen machiels]

Hello, I am using excel 2007 and I am figuring out a way to display
the date of today (and 'capture' it) in one cell Y when there is a
number (>0) entered in an other cell X.
The thing is that, when I open the excel sheet tomorrow, the date
displayed in Y still has to be the date of today and not have changed
in tomorrow's date.
Background : the purpose of the xls sheet is to create a sort of
logbook with entry's of file numbers and their entry/close dates.
....Sorry if this turns out to be a silly question : I'm just began to
learn Excel, so every help on my learning path is more than
appreciated.

 

[isabelle]

hi,

i don't know if you know VBA, because for do what you must use an event code,
for the following code, i suppose that initially there's nothing in the cell Y
and i suppose that the cell's address X is "A1" and the cell's address Y is "B1"

you have to paste the following code into ThisWorkbook
and adapt range address, save and close the Workbook
the next time the Workbook will open, the code "Workbook_Open" will be execute

Private Sub Workbook_Open()
If Range("A1") > 1 And Range("B1") = "" Then Range("B1") = Now: Range("B1").NumberFormat = "m/d/yyyy"
End Sub

 

[joeu2004]

I am using excel 2007 and I am figuring out a way to display
the date of today (and 'capture' it) in one cell Y when
there is a number (>0) entered in an other cell X.
The thing is that, when I open the excel sheet tomorrow,
the date displayed in Y still has to be the date of today
and not have changed in tomorrow's date.

Congrats! You are lightyears ahead of the nebbishes who use TODAY()
without thinking of the consequences "tomorrow".

The way you state the requirements, I believe it is impossible; or I
don't understand.

If X1>0 is true today, usually it will be true tomorrow initially when
you open the file (unless.... TBD). So if we provide a simple way to
capture today's date when X1>0 is true today, it will likely capture
tomorrow's date when X1>0 is true tomorrow.

I wonder if your requirement really is: capture the date when X1 is
changed such that it becomes >0. And I will add: remove the date
when X1 is changed such that it is no longer >0.

The low-tech solution is to simply press ctrl+; in the cell where you
want the date when you want to snapshot today's date. That is, while
pressing Ctrl, press semicolon, then release both.

But it sounds like you want something more automatic.

AFAIK, that requires a Worksheet_Change event macro.

Right-click the worksheet tab at bottom and click View Code. That
should open a VBE window with a large pane on the right. Click the
lefthand pull-down menu where it might say "(General)" and select
Worksheet.

Unfortunately, that automagically includes an unneeded event macro.
Do not delete it now. We will return to it later.

Position the cursor outside that macro, copy the following event
macro, and paste into the VBE editing pane.

'---------- begin copy below here

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, r As Range
Set r = Intersect(Target, Range("a:a"))
If Not r Is Nothing Then
Application.EnableEvents = False
For Each c In r
If IsNumeric(c) Then
With c.Offset(0, 1)
If c > 0 Then
.Value = Date
.NumberFormat = "m/dd/yyyy"
Else
.ClearContents
End If
End With
End If
Next
Application.EnableEvents = True
End If
End Sub

'---------- end copy above here

__Now__ you can delete the text for the unneeded event macro.

Note: The above design makes several assumptions that you might need
to change.

First, it assumes the values to test for >0 (called X1 above) is the
entire column A. Second, it assumes that you want to put today's date
into the corresponding cell in column B. If that is not the case, you
need to change Range("a:a") and/or Offset(0,1) accordingly.

Finally, the code assumes that the desired date format is m/dd/yyyy.
If not, you need to change that accordingly. Unfortunately, we cannot
write simply "Date", at least not in VBA with XL2003.

Before you save the Excel file, be sure to set macro security
appropriately. I like Medium level because it __always__ gives me the
opportunity to disable as well as enable macros. But arguably, that
is inconvenient.

Sorry about the excessive burden for someone who is just learning
Excel. You are asking for a fairly advance behavior, or so it seems.