Need Date to NOT advance the day after entry.

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Hello all,
How can I have the current date appear in a
cell and then be copied to another cell without the date
changing when the next day rolls around. For instance if I
use =IF(A1="","",Now()) I want the current date to appear
when I add and entry to A1 (7/22/03)and then archive it.
But that's no good when the next day rolls around, it
becomes 7/23/03. I have a macro that copies(paste special)
values only but the spreadsheet is setup to insert cells,
which then needs to be formatted, aligned, etc...Is there
a function solution that works like ctrl+; which is what I
currently do now?

Mike
 
You could try an event macro

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
If Target.Column = 1 Then
With ActiveSheet.Cells(Target.Row, 2) ' or cell of your
choice
If .Value = "" Then
.Value = Now()
.NumberFormat = "m/d/yy"
End If
End With
End If
Application.EnableEvents = True
End Sub
 
Back
Top