Create Date

[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!

 

[Bob Umlas]

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

 

[Dave R.]

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.

 

[Gord Dibben]

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

 

[Bill Kuunders]

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

 

[Eva Shanley]

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