Cant update date of 1/0/1900

[OUTPOST12]

New To Mr Excel.
I need to add a formula that will auto enter the date and time any entry is made in the adjacent cell and keep it there regardless of how many times that sheet is accessed. I need it to capture the date and time of the entry and not change.
When I use a formula like this =IF(D7<>"", IF(C7="",NOW(),C7),""), it returns a date of 1/0/1900. I have researched but cannot figure out how to get it to show the current date.
All help is very much welcome.

 

[MARK858]

What is in C7 when you get that result?

 

[Pete Willson]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("B:B"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

 

[OUTPOST12]

What is in C7 when you get that result?

Hi Mark
Thanks for the help. That is where 1/0/1900 populates. My goal was to have the date appear in that cell.

 

[OUTPOST12]

Hi Pete, I appreciate the help. But have to admit I am quite ignorant on how to add that to my excel sheet. If you can advise what i would search to learn how to do it i would appreciate it. Thanks

 

[MARK858]

Hi Mark
Thanks for the help. That is where 1/0/1900 populates. My goal was to have the date appear in that cell.

If you have the formula you posted in C7 you would get a circular reference error. Did you get one?

 

[Pete Willson]

Hi Mate,

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Then choose your used worksheet from the left Project Explorer, double click it to open the Module, and then copy and paste following VBA code into the blank Module:

Hope it Helps

 

[steve the fish]

You have to allow iterative calculations to get your formula to work. File-options-formulas then check enable iterative calculations. That will fix the date time in the formula cell to the first time you add a value to D7. If you want to refresh the date time you have to clear D7 completely then add a new value.

 

[OUTPOST12]

If you have the formula you posted in C7 you would get a circular reference error. Did you get one?

yes

" There are one or more circular references where a formula refers to its own cell either directly or indirectly. This might cause them to calculate incorrrectly.
Try removing or changing these references, or moving the formulas to different cells."

I am absolutely fine abandoning this formula for one that will put in a date and keep it static.

Thanks again.

 

[steve the fish]

The whole point of this formula is that it references itself. If it didnt it wouldnt work.