How do I track date since column M:M has been updated/changed through a formula and date applied to AB

How do I track date since column M:M has been updated/changed through a formula and date applied to AB (new to vba barely know anything) (though I just need this code then im set on my spreadsheet/ or should be if can help)

so essentially, information from varying other cells get added into cell M:M lets say sheet 4 updates the main page sheet 1 L2 from 5 to 11 in which I have a formula to add L2 and varying other cells to update M2 automatically/adjust through formula, say 56 to 62 on its own.

how do I track the value change in the cell without directly inputting data/clicking into the M cell

after researching a while I found these two but realized it doesn't fully work with what im trying to do to track varying cells some of which are updated from other sheets

I tried a 3rd unlisted but it didnt work properly and did roughly the same thing

I tried a formula but it just auto filled todays date whether there was data or not

Then this formula just it didnt work at all for some reason on like half the rows weirdly enough =IF(M2<>"",IF(AC2="",NOW(),AC2),"")

so I need a code that can track a cell that changes value from a formula and not directly updated through clicking directly into it and changing if possible

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("$D:$N"), Target) Is Nothing Then
       Range("G" & Target.Row).Value = Date
    End If
End Sub``` 


``` 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("P:P"), Target)
xOffsetColumn = 12
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"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub```

  

If you want your code to respond to changes in values caused by a formula, you must use the Calculate event, not the Change event. You didn't make it very clear whether this code should react to every change in the value in the supervised column or just once.
I assume that for every change. To detect a change in value, you need to remember what the value was before and compare it with the current one. For this purpose, I created a Static array that stores values between calls.
Column B is tested, the dates and times of the change are in E.
For testing in B4 I used the formula:

=RANDARRAY(9,,1,4,TRUE)

The code must be in the worksheet module.

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    Dim x, a As Long
    Static y
    x = Intersect(Me.UsedRange, Me.Columns("B")).Value
    x = Application.Transpose(x)
    If IsEmpty(y) Then
        y = x
    Else
        ReDim Preserve y(1 To UBound(x))
    End If
    For a = 1 To UBound(x)
        If x(a) <> y(a) Then
            y(a) = x(a)
            With Me.Range("E" & Me.UsedRange.Row + a - 1)
                .Value = Now
                .NumberFormat = "dd-mm-yyyy, hh:mm:ss"
            End With
        End If
    Next a
    Application.EnableEvents = True
End Sub