=TODAY() function to fix the date

S

starguy

I want to fix the date in a cell with a formula whenever other cell is
populated.
e.g when cell B1 is populated, cell A1 should show the date of that day
and that should not change later on.
I have seen some posts with the same question but with no answer.
any guru on this..??
 
K

Kevin B

In cell A1 place this formula:

=IF(ISBLANK(E1),"",DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())))

If the result displays as an integer, format the cell using the date format
of choice.
 
B

Bernie Deitrick

Starguy,

You need to use a worksheet change event to do that: for example, for
any cell in column B, the date when the entry is made or changed is stored in column A
using this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each myCell In Intersect(Target, Range("B:B"))
Cells(myCell.Row, 1).Value = Now
Cells(myCell.Row, 1).NumberFormat = "mm/dd/yy hh:mm:ss"
Next myCell
Application.EnableEvents = True
End Sub

Copy this code, right-click on the worksheet tab, select "View Code" and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP
 
Top