Create Date

  • Thread starter Thread starter Eva Shanley
  • Start date Start date
E

Eva Shanley

Is there a way to insert the current date into a cell and
not have it update the following day? For example, an
invoice number is keyed in cell A1, and when it's entered
the current date displays in B1. So I need to know how to
insert the date based on whether or not A1 is empty, and
also have the date remain the same. Thanks as always!
 
You're going to need a simple macro to do it.
Right-click the sheet tab, select View Code, then paste this in:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then Range("B1").Value = Date
End Sub

If you don't really mean A1, then change the above accordingly.

Bob Umlas
Excel MVP
 
Hi Bob, that macro works good, but it only works on row 1, and it will
update the date anytime the invoice # (whatever) is updated, rather than
just the first time it's entered.
 
Eva

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col A
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" Then
Excel.Range("B" & n).Value = Now
End If
End If
enditall:
End Sub

OR if you want to be able to edit column A without B updating.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Col B time will not change if data in Col A is edited
On Error GoTo enditall
If Target.Cells.Column = 1 Then
n = Target.Row
If Excel.Range("A" & n).Value <> "" _
And Excel.Range("B" & n).Value = "" Then
Excel.Range("B" & n).Value = Now
End If
End If
enditall:
End Sub

Gord Dibben XL2002
 
A couple of very smart macro's from Gord
Thank you
I had to add<<< Dim n As Long>>> at the start.
to make it work.
And Eva will have to know that she can format the B column as a date rather
then time if she so wishes.

My solution involved an if function in the B column and a copy paste special
values macro when closing the workbook.
Rather long winded compared to the options below...

Regards
BillK
 
Thanks very much to Gord and Bob for the answer to my
question. Code worked great and I learned more!
 
Back
Top