Formula: days since sheet was last modified?

[ame12]

Howdy... is there a formula that will display the number of days since the sheet was last updated? I tried

days360(today(),now())

days360(now(),today())

it just always displays 0 - any help would be great, cool, you name it!

 

[rmikesmith]

ame12,

You are getting zero each time because the days360 function is subtracting todays date from itself; i.e. TODAY & NOW represent the same date.

I don't see how you can do what you want without using some VBA code. Essentially, you need a procedure that saves a copy of today's date in a worksheet somewhere. This procedure would need to be fired by the Worksheet_Change event. Another procedure or function could then reference that worksheet cell and subtract its value from the current date. I can put something together if you want. Post back.

Regards,
M. Smith

 

[ame12]

Dear M. Smith, yes - any detail you could provide as to how to do this would be very much appreciated.

Thank you!

 

[Hasit]

Take a look at this as it will start you down the right road.

http://www.j-walk.com/ss/excel/tips/tip57.htm

 

[rmikesmith]

ame12,

I have assumed here that you want to track the number of days since a particular worksheet has been modified. This could be one of several in a given workbook. For this example, create a new workbook and insert two worksheets. Name them "Sheet_To_Track" and "Store_Modify_Date". In the VB Editor, insert a standard Code Module and copy the following procedures and function:

Sub Test()
' Run this to test the code
  MsgBox "Number of days since worksheet 'Sheet_To_Track' was modified:  " & DaysSinceModified, vbInformation + vbOKOnly, "Sheet Modification Info"
End Sub


Sub SaveModifiedDate()
  ThisWorkbook.Worksheets("Store_Modify_Date").Range("A1").Value = Date
End Sub


Function DaysSinceModified() As Integer
Dim LastModifiedDate As Date

  If IsDate(ThisWorkbook.Worksheets("Store_Modify_Date").Range("A1").Value) Then
    LastModifiedDate = ThisWorkbook.Worksheets("Store_Modify_Date").Range("A1").Value
    DaysSinceModified = Date - LastModifiedDate
  Else
    DaysSinceModified = 0
  End If
End Function

Place a call to procedure SaveModifiedData in the Sheet_To_Track's Code Module, inside the Worksheet_Change event procedure. It should look like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  SaveModifiedDate
End Sub

Whenever a change is made to worksheet "Sheet_To_Track", it will fire procedure SaveModifiedDate. You can then use the function DaysSinceModified to return the elapsed time in days since the change.

Let me know if you need more assistance. Good Luck

M. Smith

 

[larryww]

How about doing this on BeforeSave instead of Change (since I don't see an AfterSave)

 

[rmikesmith]

larryww,

Since I took ame12's objective to be to determine days since a particular worksheet has been modified, I wanted my "date stamp" procedure to be fired whenever a change was made to that worksheet; hence my choice of the Worksheet_Change event handler. I will say though that in order for my code to work across multiple workbook sessions, the workbook must be saved before exiting, if the modification date was updated in a particular session. I made no provision for that in my example. Depending on how the user makes changes in the workbook, that could be a sticky proposition; i.e. if you automatically save the workbook, you may inadvertently save changes the end-user didn't wish to save. If your code does nothing, the end-user could decide to close the workbook without saving, thereby discarding the modification date. I don't have a good solution for this...perhaps writing the modification date to a separate file (which has its own problems).

Regards,
M. Smith

 

[ame12]

RMikeSmith - and Larry - thank you so much for the code and link... I will try this asap and will post any results. Again, thank you for your incredible responses! ...!...

 

[ame12]

RMikeSmith - almost have it working... i defined the functions in a new sheet ("sheet3&quot. i'm tracking sheet1 - all the labels have been changed to refer to sheet1. i defined the Worksheet_Change function in sheet1's worksheet-chnage event. however, when it runs, i get "compile error - sub or function not found" with SaveModifiedDate highlighted. being a rookie at excel vba, can't figure out why it can't find SaveModifiedDate - which is a general function tied to sheet3...

Thanks for all the help!

 

[rmikesmith]

ame12,

I appears to me that you have declared the procedure SaveModifiedDate and possibly the function DaysSinceModified in the wrong location. You wrote: i defined the functions in a new sheet ("sheet3&quot. These should be placed into a standard Code Module. Do this by bringing up the VB Editor then selecting Module from the Insert menu. Be sure to also remove these declarations from the Sheet3 code module.

Post back on how you make out.

M. Smith

 

[ame12]

Still can't quite get it to work. Here's what I have in sheet3 (you have to save the modified-date of sheet1 in sheet3, otherwise you get a recursion/stack overflow):

Sub SaveModifiedDate()
ThisWorkbook.Worksheets("Sheet3&quot.Range("A1&quot.Value = Date
End Sub

Function DaysSinceModified() As Integer
Dim LastModifiedDate As Date

If IsDate(ThisWorkbook.Worksheets("Sheet3&quot.Range("A1&quot.Value) Then
LastModifiedDate = ThisWorkbook.Worksheets("Sheet3&quot.Range("A1&quot.Value
DaysSinceModified = Date - LastModifiedDate
Else
ThisWorkbook.Worksheets("Sheet3&quot.Range("A1&quot.Value = Date
DaysSinceModified = 0
End If
End Function

And here's what's tied to sheet1's worksheet-change event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Sheet3.SaveModifiedDate
MsgBox "Number of days since worksheet 'Sheet1' was modified: " & Str(DaysSinceModified), vbInformation + vbOKOnly, "Sheet Modification Info"
End Sub

Problem 1 came in that when I tried to refer a call (in sheet1) to =DaysSinceModified , it came up as unrecognized. Okay, that's fine since it's in sheet3. So I tried =sheet3.DaysSinceModified... nope.

So then I defined a general function in sheet1 that just calls sheet3.DaysSinceModified and returns its value. Now what happens is that it appears to run (it fires the message-box so I know it ran)... but it simply displays #NAME? in the cell rather than the int return value.

So... I'm very close, but unfamiliarity with the nuances of Excel/VBA calls is still mystifying me

Thanks again for all the help.

 

[ame12]

Arghhh... what's really strange about the cell's function call is that I used the formula builder to help me construct the right syntax. It shows me the correct user-defined functions (both sheet3's original and sheet1's equivalent). I choose either one and I get a "paperclip" error saying "That name is not valid". And yet it's the one prompting me to choose it in the listbox!

Whaaa???

 

[rmikesmith]

ame12,

No offense, but you're making this too complicated. Both SaveModifiedDate and DaysSinceModified need to be in a standard code module, not a worksheet code module. As a side benefit (in addition to allowing this to work correctly) you can then use DaysSinceModified anywhere in your worksheet, just as you would a built-in worksheet function.

If you like, post your email addy and I'll send you a copy of my workbook that implements this.

Regards,
Mike

 

[ame12]

Thanks. For a newcomer to Excel development (but pretty good at general software development) the distinction between a standard-code-module and a worksheet-code-module seems to be hidden from me. My email address is support@badblue.com - and again it is very much appreciated.

And if you don't want to go to the trouble of emailing the XLS, feel free just to post how one would create a standard code module (or modify my existing routines to be standard rather than worksheet).

I can assure you that the difference between the two is not at all obvious, given the fact that I created everything directly from Excel and was prompted by Excel with the user-defined fuctions I created. So it knew about them... but wouldn't link to them... not at all clear.

Thanks!