The basic idea is that a global array, in which the addresses of all monitored ranges (past or present) are stored with their most recent update dates, allows the function and the Worksheet_Change Sub to interact. The Worksheet_Change Sub updates this array by checking the changed range against all stored ranges. The function looks for the monitored range in the array and returns the stored change-date if it's found. Otherwise, it will return today's date (which will then be added to the array).

Also, to prevent timestamps being lost when the workbook is closed and the array of timestamps is deallocated, the array must be written to a sheet on the Workbook_Close event, and then rewritten to the array on the Workbook_Open event.

In a module, paste the following code.

Public funcInstances() As Variant
Public Function MOD_DATE_OF(monitor As Range)
Application.Volatile True
Dim i As Long
Dim tmpArray() As Variant
If Not IsDimensioned(funcInstances) Then
ReDim funcInstances(1 To 1, 1 To 2) As Variant
funcInstances(1, 1) = monitor.Address
funcInstances(1, 2) = Date
Else
For i = 1 To UBound(funcInstances, 1)
If funcInstances(i, 1) = monitor.Address Then
MOD_DATE_OF = Format(funcInstances(i, 2), "yyyy-mm-dd")
Exit Function
End If
Next i
tmpArray = ExpandArray(funcInstances, 1, 1, "")
Erase funcInstances
funcInstances = tmpArray
funcInstances(UBound(funcInstances, 1), 1) = monitor.Address
funcInstances(UBound(funcInstances, 1), 2) = Date
End If
MOD_DATE_OF = Format(funcInstances(UBound(funcInstances, 1), 2), "yyyy-mm-dd")
End Function
'ExpandArray() is the work of Chip Pearson. Code copied from http://www.cpearson.com/excel/vbaarrays.htm
Function ExpandArray(Arr As Variant, WhichDim As Long, AdditionalElements As Long, _
FillValue As Variant) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' ExpandArray
' This expands a two-dimensional array in either dimension. It returns the result
' array if successful, or NULL if an error occurred. The original array is never
' changed.
' Parameters:
' --------------------
' Arr is the array to be expanded.
'
' WhichDim is either 1 for additional rows or 2 for
' additional columns.
'
' AdditionalElements is the number of additional rows or columns
' to create.
'
' FillValue is the value to which the new array elements should be
' initialized.
'
' You can nest calls to Expand array to expand both the number of rows and
' columns. E.g.,
'
' C = ExpandArray(ExpandArray(Arr:=A, WhichDim:=1, AdditionalElements:=3, FillValue:="R"), _
' WhichDim:=2, AdditionalElements:=4, FillValue:="C")
' This first adds three rows at the bottom of the array, and then adds four
' columns on the right of the array.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim Result As Variant
Dim RowNdx As Long
Dim ColNdx As Long
Dim ResultRowNdx As Long
Dim ResultColNdx As Long
Dim NumRows As Long
Dim NumCols As Long
Dim NewUBound As Long
Const ROWS_ As Long = 1
Const COLS_ As Long = 2
''''''''''''''''''''''''''''
' Ensure Arr is an array.
''''''''''''''''''''''''''''
If IsArray(Arr) = False Then
ExpandArray = Null
Exit Function
End If
'''''''''''''''''''''''''''''''''
' Ensure the dimension is 1 or 2.
'''''''''''''''''''''''''''''''''
Select Case WhichDim
Case 1, 2
Case Else
ExpandArray = Null
Exit Function
End Select
''''''''''''''''''''''''''''''''''''
' Ensure AdditionalElements is > 0.
' If AdditionalElements < 0, return NULL.
' If AdditionalElements = 0, return Arr.
''''''''''''''''''''''''''''''''''''
If AdditionalElements < 0 Then
ExpandArray = Null
Exit Function
End If
If AdditionalElements = 0 Then
ExpandArray = Arr
Exit Function
End If
NumRows = UBound(Arr, 1) - LBound(Arr, 1) + 1
NumCols = UBound(Arr, 2) - LBound(Arr, 2) + 1
If WhichDim = ROWS_ Then
'''''''''''''''
' Redim Result.
'''''''''''''''
ReDim Result(LBound(Arr, 1) To UBound(Arr, 1) + AdditionalElements, LBound(Arr, 2) To UBound(Arr, 2))
''''''''''''''''''''''''''''''
' Transfer Arr array to Result
''''''''''''''''''''''''''''''
For RowNdx = LBound(Arr, 1) To UBound(Arr, 1)
For ColNdx = LBound(Arr, 2) To UBound(Arr, 2)
Result(RowNdx, ColNdx) = Arr(RowNdx, ColNdx)
Next ColNdx
Next RowNdx
'''''''''''''''''''''''''''''''
' Fill the rest of the result
' array with FillValue.
'''''''''''''''''''''''''''''''
For RowNdx = UBound(Arr, 1) + 1 To UBound(Result, 1)
For ColNdx = LBound(Arr, 2) To UBound(Arr, 2)
Result(RowNdx, ColNdx) = FillValue
Next ColNdx
Next RowNdx
Else
'''''''''''''''
' Redim Result.
'''''''''''''''
ReDim Result(LBound(Arr, 1) To UBound(Arr, 1), UBound(Arr, 2) + AdditionalElements)
''''''''''''''''''''''''''''''
' Transfer Arr array to Result
''''''''''''''''''''''''''''''
For RowNdx = LBound(Arr, 1) To UBound(Arr, 1)
For ColNdx = LBound(Arr, 2) To UBound(Arr, 2)
Result(RowNdx, ColNdx) = Arr(RowNdx, ColNdx)
Next ColNdx
Next RowNdx
'''''''''''''''''''''''''''''''
' Fill the rest of the result
' array with FillValue.
'''''''''''''''''''''''''''''''
For RowNdx = LBound(Arr, 1) To UBound(Arr, 1)
For ColNdx = UBound(Arr, 2) + 1 To UBound(Result, 2)
Result(RowNdx, ColNdx) = FillValue
Next ColNdx
Next RowNdx
End If
''''''''''''''''''''
' Return the result.
''''''''''''''''''''
ExpandArray = Result
End Function
'IsDimensioned() is the work of StackOverflow user @Thomas. Code copied from http://stackoverflow.com/a/5480690/657668
Public Function IsDimensioned(vValue As Variant) As Boolean
On Error Resume Next
If Not IsArray(vValue) Then Exit Function
Dim i As Integer
i = UBound(vValue)
IsDimensioned = Err.Number = 0
End Function

In the appropriate Worksheet module, paste the following code.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim j As Long
If IsDimensioned(funcInstances) Then
For j = 1 To UBound(funcInstances, 1)
If Not Intersect(Target, Range(funcInstances(j, 1))) Is Nothing Then
funcInstances(j, 2) = Date
End If
Next j
Me.Calculate
End If
Application.EnableEvents = True
End Sub

This works. If the time needs to be recorded as well, a Long will be problematic, when using Now, so making modDate a Variant will work to show time
–
datatooJun 6 '12 at 17:51

It works for one instance as you said. Though the idea of having it as a function was exactly so I could monitor multiple points separately. Any chance you could add that functionality? :) thanks.
–
flpgdtJun 6 '12 at 18:03

@flpgdt Ha, of course that's what you wanted! I have an idea to test, but it may take a little while. If it's too time-consuming for me, I may post the basic idea here in a little while.
–
ExcellllJun 6 '12 at 18:05

@Excellll Thanks man. I will be tinkering with it myself when I have time, but will take a long while to get into a satisfactory solution.
–
flpgdtJun 6 '12 at 18:11

@flpgdt I added a full solution. It took a little work, but I think it's complete. Let me know if it works for you.
–
ExcellllJun 6 '12 at 20:28