Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that displays the documents modified date
Is there a formula that displays an excel documents modified date?
=today() displays the current date but I'm looking for a way to display in a cell the date the document was last modified. can someone help? -- Thanks, raisorpr. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that displays the documents modified date
Try this tiny UDF:
Function lastsaved() As Double lastsaved = ActiveWorkbook.BuiltinDocumentProperties(12) End Function -- Gary''s Student - gsnu200781 "raisorpr" wrote: Is there a formula that displays an excel documents modified date? =today() displays the current date but I'm looking for a way to display in a cell the date the document was last modified. can someone help? -- Thanks, raisorpr. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that displays the documents modified date
Time Stamp:
Right-click on your worksheet tab and paste this in the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$1:$b$400")) Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Worksheets("Sheet2") .Select .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select ActiveCell.Value = Target.Address ActiveCell.Offset(0, 1).Select ActiveCell.Value = Target.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() ActiveCell.NumberFormat = "mm/dd/yy" ActiveCell.Offset(0, 1).Select ActiveCell.Value = InputBox("You've made a change to the Rates tab. Please enter your name here for historical purposes.") Application.EnableEvents = True Application.ScreenUpdating = True End With End If End Sub Track Changes: You can try this too: Function MyUserName() As String MyUserName = Environ("UserName") End Function Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$1:$BB$4000")) Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Worksheets("Sheet2") ..Select ..Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select ActiveCell.Value = Target.Address ActiveCell.Offset(0, 1).Select ActiveCell.Value = Target.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() ActiveCell.NumberFormat = "mm/dd/yy" ActiveCell.Offset(0, 1).Select ActiveCell.Value = MyUserName() Application.EnableEvents = True Application.ScreenUpdating = True End With End If End Sub Finally, one more version: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Regards, Ryan--- -- RyGuy "raisorpr" wrote: Is there a formula that displays an excel documents modified date? =today() displays the current date but I'm looking for a way to display in a cell the date the document was last modified. can someone help? -- Thanks, raisorpr. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that displays the documents modified date
I tried coping and pasting this code but didn't work.
Not sure what I did wrong. I was trying to get the modified date to display in Cell A1 of Sheet1. -- Thanks, raisorpr. "Gary''s Student" wrote: Try this tiny UDF: Function lastsaved() As Double lastsaved = ActiveWorkbook.BuiltinDocumentProperties(12) End Function -- Gary''s Student - gsnu200781 "raisorpr" wrote: Is there a formula that displays an excel documents modified date? =today() displays the current date but I'm looking for a way to display in a cell the date the document was last modified. can someone help? -- Thanks, raisorpr. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula that displays the documents modified date
I tried coping and pasting this code but didn't work.
Not sure what I did wrong. I was trying to get the modified date to display in Cell A1 of Sheet1. -- Thanks, raisorpr. "ryguy7272" wrote: Time Stamp: Right-click on your worksheet tab and paste this in the window that opens: Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$1:$b$400")) Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Worksheets("Sheet2") .Select .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select ActiveCell.Value = Target.Address ActiveCell.Offset(0, 1).Select ActiveCell.Value = Target.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() ActiveCell.NumberFormat = "mm/dd/yy" ActiveCell.Offset(0, 1).Select ActiveCell.Value = InputBox("You've made a change to the Rates tab. Please enter your name here for historical purposes.") Application.EnableEvents = True Application.ScreenUpdating = True End With End If End Sub Track Changes: You can try this too: Function MyUserName() As String MyUserName = Environ("UserName") End Function Private Sub Worksheet_Change(ByVal Target As Range) If Not Application.Intersect(Target, Range("$A$1:$BB$4000")) Is Nothing Then Application.EnableEvents = False Application.ScreenUpdating = False With Worksheets("Sheet2") .Select .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select ActiveCell.Value = Target.Address ActiveCell.Offset(0, 1).Select ActiveCell.Value = Target.Value ActiveCell.Offset(0, 1).Select ActiveCell.Value = Now() ActiveCell.NumberFormat = "mm/dd/yy" ActiveCell.Offset(0, 1).Select ActiveCell.Value = MyUserName() Application.EnableEvents = True Application.ScreenUpdating = True End With End If End Sub Finally, one more version: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count 1 Then Exit Sub If Not Intersect(Range("A2:A10"), .Cells) Is Nothing Then Application.EnableEvents = False If IsEmpty(.Value) Then .Offset(0, 1).ClearContents Else With .Offset(0, 1) .NumberFormat = "dd mmm yyyy hh:mm:ss" .Value = Now End With End If Application.EnableEvents = True End If End With End Sub Regards, Ryan--- -- RyGuy "raisorpr" wrote: Is there a formula that displays an excel documents modified date? =today() displays the current date but I'm looking for a way to display in a cell the date the document was last modified. can someone help? -- Thanks, raisorpr. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for file modified date | Excel Discussion (Misc queries) | |||
date that a row is modified | Excel Discussion (Misc queries) | |||
date modified | Excel Discussion (Misc queries) | |||
Getting last Modified documents/worksheets | Excel Discussion (Misc queries) | |||
Formula window displays correct answer while cell displays incorre | Excel Worksheet Functions |