Need Fixed Date Formula

  • Thread starter Thread starter wwoodall
  • Start date Start date
W

wwoodall

Users submit new lines of info on our excel database. We want the dat
to self-generate when the adjacent fields are filled out, and we wan
the date to be fixed to the day the line was filled out.

=now() changes daily, so we cannot go back and see when the data wa
filled in.

We don't want to leave it a text field, because users often do not fil
the field in.

A formula would be greatly appreciated. Feel free to E-mai
(e-mail address removed)
 
I don't think you can do this with formulas. But you could use an event macro:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("b:IV")) Is Nothing Then Exit Sub

On Error GoTo errHandler:

Application.EnableEvents = False

With Me.Cells(Target.Row, "A")
If Application.CountA(.Offset(0, 1).Resize(1, Me.Columns.Count - 1)) _
= 0 Then
'they've emptied the cells
.ClearContents
Else
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End If
End If
End With

errHandler:
Application.EnableEvents = True

End Sub

I used column A for my date cell. Any change to column B to IV (255 columns)
will put the date in column A (if it's empty). If it's filled in, it doesn't
damage the existing value.

rightclick on the worksheet's tab that should have this behavior. Paste in the
code and back to excel to try it out.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Chip Pearson also has some nice notes about workbook/worksheet events at:
http://www.cpearson.com/excel/events.htm
 
Back
Top