"last modified" timestamp function in excel 2003

i need to insert a function for a cell that captures the last time the file 
was *modified* (not last accessed or last saved).  i am sure there exists 
such a thing, but was surprised to not find it in the list of built-in date & 
time functions.
-- 

thanks so much!
veek
0
veek (11)
7/24/2008 9:43:02 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
555 Views

Similar Articles

[PageSpeed] 55

If you really want to keep up with latest time that the workbook was 
modified, then you need to work through the Workbook_SheetChange() event 
handler.  With code similar to this (change the sheet name and cell address 
to point to where ever you want this time stamp to appear)

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim modLocation As Range
  Application.EnableEvents = False
  Set modLocation = Worksheets("Sheet1").Range("B2")
  modLocation = Now()
  Set modLocation = Nothing
  Application.EnableEvents = False
End Sub

This page will help you get the code into the proper location in your 
workbook:
http://www.jlathamsite.com/Teach/WorkbookCode.htm

Hope this helps.




"veek" wrote:

> i need to insert a function for a cell that captures the last time the file 
> was *modified* (not last accessed or last saved).  i am sure there exists 
> such a thing, but was surprised to not find it in the list of built-in date & 
> time functions.
> -- 
> 
> thanks so much!
> veek
0
Utf
7/24/2008 11:26:01 PM
There is no "modified date" to grab.

When you open a workbook the modified date changes to current. 

If you close the workbook with no changes or save, the modified date will
revert to last modified date which is last saved date.

Open Windows Explorer and find a workbook saved before today and note the
modified date, 

Now open that workbook and View>Refresh in Windows Explorer. 

Date will change to current. 

Close the workbook without save and View>Refresh in WE. 

Note date changes back to original. 


Gord Dibben  MS Excel MVP 




On Thu, 24 Jul 2008 14:43:02 -0700, veek <veek@discussions.microsoft.com>
wrote:

>i need to insert a function for a cell that captures the last time the file 
>was *modified* (not last accessed or last saved).  i am sure there exists 
>such a thing, but was surprised to not find it in the list of built-in date & 
>time functions.

0
Gord
7/24/2008 11:39:23 PM
I think that the second Application.EnableEvents  should be TRUE

Set modLocation = Nothing
Application.EnableEvents = True
End Sub

-- 
Regards,

OssieMac


"JLatham" wrote:

> If you really want to keep up with latest time that the workbook was 
> modified, then you need to work through the Workbook_SheetChange() event 
> handler.  With code similar to this (change the sheet name and cell address 
> to point to where ever you want this time stamp to appear)
> 
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>   Dim modLocation As Range
>   Application.EnableEvents = False
>   Set modLocation = Worksheets("Sheet1").Range("B2")
>   modLocation = Now()
>   Set modLocation = Nothing
>   Application.EnableEvents = False
> End Sub
> 
> This page will help you get the code into the proper location in your 
> workbook:
> http://www.jlathamsite.com/Teach/WorkbookCode.htm
> 
> Hope this helps.
> 
> 
> 
> 
> "veek" wrote:
> 
> > i need to insert a function for a cell that captures the last time the file 
> > was *modified* (not last accessed or last saved).  i am sure there exists 
> > such a thing, but was surprised to not find it in the list of built-in date & 
> > time functions.
> > -- 
> > 
> > thanks so much!
> > veek
0
OssieMac (238)
7/25/2008 2:59:01 AM
jlatham - thank you so much for offering assistance.  i followed the 
user-friendly instructions in your link below but i'm not getting any 
timestamp in the field i specified - so i know i am doing something wrong.  
the worksheet is named "Communications-Milestones" and the cell address is C6 
so the code i entered looks like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  Dim modLocation As Range
  Application.EnableEvents = False
  Set modLocation = Worksheets("Communications-Milestones").Range("C6")
  modLocation = Now()
  Set modLocation = Nothing
  Application.EnableEvents = False
End Sub

I tried it inside of the default _Open event code and also I tried it after 
removing the _Open code - and cannot get it to work either way.

I had originally just set the Now() function for field C6 but wouldn't that 
just capture the current time and not the time of last mod?
-- 

thanks so much!
veek


"JLatham" wrote:

> If you really want to keep up with latest time that the workbook was 
> modified, then you need to work through the Workbook_SheetChange() event 
> handler.  With code similar to this (change the sheet name and cell address 
> to point to where ever you want this time stamp to appear)
> 
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>   Dim modLocation As Range
>   Application.EnableEvents = False
>   Set modLocation = Worksheets("Sheet1").Range("B2")
>   modLocation = Now()
>   Set modLocation = Nothing
>   Application.EnableEvents = False
> End Sub
> 
> This page will help you get the code into the proper location in your 
> workbook:
> http://www.jlathamsite.com/Teach/WorkbookCode.htm
> 
> Hope this helps.
> 
> 
> 
> 
> "veek" wrote:
> 
> > i need to insert a function for a cell that captures the last time the file 
> > was *modified* (not last accessed or last saved).  i am sure there exists 
> > such a thing, but was surprised to not find it in the list of built-in date & 
> > time functions.
> > -- 
> > 
> > thanks so much!
> > veek
0
veek (11)
7/25/2008 3:41:17 PM
Firstly, as per my previous post, there is an error in the code. (I am sure 
the error was inadvertant and we all make them). Change the second  last line 
of code Application.EnableEvents = False
 to 
Application.EnableEvents = True

When events have been turned off, if they are not turned on again in code, 
then they remain off for the remainder of the Excel session and no events 
will run until events is turned on again.

If events are turned off and not turned back on again either due to code 
error or a failure of the code before they are turned back on then it is then 
necessary to run a separate sub on its own like the following to turn them 
back on again:-

Sub Reset_Events()
Application.EnableEvents = True
End Sub

Secondly, in the VBA editor, did you select ThisWorkbook in the VBA Project 
Explorer and place the code in there?

I tested the code (with my modification) and it works.

-- 
Regards,

OssieMac


"veek" wrote:

> jlatham - thank you so much for offering assistance.  i followed the 
> user-friendly instructions in your link below but i'm not getting any 
> timestamp in the field i specified - so i know i am doing something wrong.  
> the worksheet is named "Communications-Milestones" and the cell address is C6 
> so the code i entered looks like this:
> 
> Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>   Dim modLocation As Range
>   Application.EnableEvents = False
>   Set modLocation = Worksheets("Communications-Milestones").Range("C6")
>   modLocation = Now()
>   Set modLocation = Nothing
>   Application.EnableEvents = False
> End Sub
> 
> I tried it inside of the default _Open event code and also I tried it after 
> removing the _Open code - and cannot get it to work either way.
> 
> I had originally just set the Now() function for field C6 but wouldn't that 
> just capture the current time and not the time of last mod?
> -- 
> 
> thanks so much!
> veek
> 
> 
> "JLatham" wrote:
> 
> > If you really want to keep up with latest time that the workbook was 
> > modified, then you need to work through the Workbook_SheetChange() event 
> > handler.  With code similar to this (change the sheet name and cell address 
> > to point to where ever you want this time stamp to appear)
> > 
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >   Dim modLocation As Range
> >   Application.EnableEvents = False
> >   Set modLocation = Worksheets("Sheet1").Range("B2")
> >   modLocation = Now()
> >   Set modLocation = Nothing
> >   Application.EnableEvents = False
> > End Sub
> > 
> > This page will help you get the code into the proper location in your 
> > workbook:
> > http://www.jlathamsite.com/Teach/WorkbookCode.htm
> > 
> > Hope this helps.
> > 
> > 
> > 
> > 
> > "veek" wrote:
> > 
> > > i need to insert a function for a cell that captures the last time the file 
> > > was *modified* (not last accessed or last saved).  i am sure there exists 
> > > such a thing, but was surprised to not find it in the list of built-in date & 
> > > time functions.
> > > -- 
> > > 
> > > thanks so much!
> > > veek
0
OssieMac (238)
7/25/2008 9:55:03 PM
Thanks for catching that - definitely a problem creator. 

My sincere apologies to the OP.

"OssieMac" wrote:

> Firstly, as per my previous post, there is an error in the code. (I am sure 
> the error was inadvertant and we all make them). Change the second  last line 
> of code Application.EnableEvents = False
>  to 
> Application.EnableEvents = True
> 
> When events have been turned off, if they are not turned on again in code, 
> then they remain off for the remainder of the Excel session and no events 
> will run until events is turned on again.
> 
> If events are turned off and not turned back on again either due to code 
> error or a failure of the code before they are turned back on then it is then 
> necessary to run a separate sub on its own like the following to turn them 
> back on again:-
> 
> Sub Reset_Events()
> Application.EnableEvents = True
> End Sub
> 
> Secondly, in the VBA editor, did you select ThisWorkbook in the VBA Project 
> Explorer and place the code in there?
> 
> I tested the code (with my modification) and it works.
> 
> -- 
> Regards,
> 
> OssieMac
> 
> 
> "veek" wrote:
> 
> > jlatham - thank you so much for offering assistance.  i followed the 
> > user-friendly instructions in your link below but i'm not getting any 
> > timestamp in the field i specified - so i know i am doing something wrong.  
> > the worksheet is named "Communications-Milestones" and the cell address is C6 
> > so the code i entered looks like this:
> > 
> > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >   Dim modLocation As Range
> >   Application.EnableEvents = False
> >   Set modLocation = Worksheets("Communications-Milestones").Range("C6")
> >   modLocation = Now()
> >   Set modLocation = Nothing
> >   Application.EnableEvents = False
> > End Sub
> > 
> > I tried it inside of the default _Open event code and also I tried it after 
> > removing the _Open code - and cannot get it to work either way.
> > 
> > I had originally just set the Now() function for field C6 but wouldn't that 
> > just capture the current time and not the time of last mod?
> > -- 
> > 
> > thanks so much!
> > veek
> > 
> > 
> > "JLatham" wrote:
> > 
> > > If you really want to keep up with latest time that the workbook was 
> > > modified, then you need to work through the Workbook_SheetChange() event 
> > > handler.  With code similar to this (change the sheet name and cell address 
> > > to point to where ever you want this time stamp to appear)
> > > 
> > > Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> > >   Dim modLocation As Range
> > >   Application.EnableEvents = False
> > >   Set modLocation = Worksheets("Sheet1").Range("B2")
> > >   modLocation = Now()
> > >   Set modLocation = Nothing
> > >   Application.EnableEvents = False
> > > End Sub
> > > 
> > > This page will help you get the code into the proper location in your 
> > > workbook:
> > > http://www.jlathamsite.com/Teach/WorkbookCode.htm
> > > 
> > > Hope this helps.
> > > 
> > > 
> > > 
> > > 
> > > "veek" wrote:
> > > 
> > > > i need to insert a function for a cell that captures the last time the file 
> > > > was *modified* (not last accessed or last saved).  i am sure there exists 
> > > > such a thing, but was surprised to not find it in the list of built-in date & 
> > > > time functions.
> > > > -- 
> > > > 
> > > > thanks so much!
> > > > veek
0
Utf
7/26/2008 4:13:01 AM
Reply:

Similar Artilces:

dynamically enter name of last modified by in spreadsheet on save
Is there a way to have excel change the name in the footer of a spreadsheet to the name of the person who last modified the the spreadsheet and also the time of the last change. Not the time it was last viewed... but specifically modified. You could write code for the BeforeSave event (more on that can be found in online VBA help) to change the footer. That would assume that the user enabled macros when the workbook was opened. If he didn't then no macros will run and you cannot enforce this. -- Jim "HW" <HW@discussions.microsoft.com> wrote in message news:17C4...

Account Owner last Changed (or) Last modified date
is there any field in the CRM Database is capturing Account Owner last modified date. check "modifiedon" and "modifiedby" fields which capture the last modification to the record and are present in most of the entities. "srini" wrote: > is there any field in the CRM Database is capturing Account Owner last > modified date. > > Amit, those two fields are going to be used only if there are any changes in the entity Information. my question is is there any field, rather is there any way to capture Account owner last modified (or) changed. ex:Us...

Date created and date last modified
In previous version of Word, you could check the "date created" and "date last modified" in the document by drilling down through properties, and then summary. This information is not available in Word 2007 documents. Is there any way to discover these attributes of a file in 2007? Click the Office button, click Prepare, and then click Document Properties. In the pane that displays, click Document Properties, and finally click Advanced Properties. You'll find the data you are looking for on the Summary tab. -- Stefan Blom Microsoft Word MVP ...

Getting last Modified documents/worksheets
I used a previous version of excel on a win98 pc. At the end of the week I would recall all the documents created and saved in the past week for proofreading etc. This was very fast and easy. However, I bought a WinXP laptop and transfered all my documents onto it. It had office 2003 Pro installed so I thought my latest saved files would be even easier and faster to retrieve. To my utter dismay the reverse happened. Retrieving latest documents over the weekend has been a nightmare because I now have sit and wait for at least 20 minuites to have only 4 out of 20 files found and listed....

Date Last Modified
I have created a Word (2002) document in which I have inserted a field which picks up the document properties. I have inserted the date the document was last modified, however, when I modify the document and save it, it does not appear to automatically change the date/time. Can anyone let me know if this date can be forced to automatically change if the document is modified? TIA Note that if the field is in the header or footer, it will update when you open the document. To trigger an update of the field at any time, you can switch to Print preview and then back to your favorite...

Update of the field "Last Modified On"
As far as I remember, in MSCRM 1.2 when you change the access level of a role privilege in an entity (i.e. account, case, etc), the system updates the field "Last Modified On" for all records of this entity with the current date. This is very serious problem in case you use this field for reporting issues. Do you have any idea whether this do happen in MSCRM 1.2 and if so, has this issue been resolved in version 3.0 ??? Thank you very much... I believe it still hapens. -- Matt Parks MVP - Microsoft CRM "George" <George@discussions.microsoft.com> wrote in...

last modified on appointments
When I create an appointment in my calendar it lists the "last modifed by" as my old name. The name attached to my calendar is correct thought. How do I update this? This is with an exchange account? it picks it up from the active directory. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.E...

Calendar last modified
Outlook 2007 - A calendar is used by a Executive who has 2 admins both of who create and modify entries in the Execs Calendar. Is there any way to tell who last modified the entry ? No, not who modified it, only who created it and what time it was last modified. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQU...

date last modified
Hi Have had a look around the net for a solution - they are a few around but I can't seem to get them to work I need to put the date last modified either in a cell or as a footer - can anyone help? I am running excel 2007 thanks Try this User Defined FUnction: Function lastsaved() As Double lastsaved = ActiveWorkbook.BuiltinDocumentProperties(12) End Function -- Gary''s Student - gsnu2007k "JD" wrote: > Hi > > Have had a look around the net for a solution - they are a few around but I > can't seem to get them to work > > I need to put...

Shared Contacts
Hello. I have a Shared contacts list. When these contacts are accessed, there is a "last modified" date on the contact that is automatically updated. Unfortunately, the "person" that modified the contact is usually wrong. The update gets the date correct but most of the time the person modified shows as the owner of the contact list. Any suggestions would be greatly appreciated. Thank you. clk;90178 Wrote: > Hello. I have a Shared contacts list. When these contacts are > accessed, there is a "last modified" date on the contact that is > automa...

Is there a way to track user who last modified a PO?
Hi, is there a way to track a user who last modified a specific PO, aside from using Activity Tracking in GP? We have a PO that was created by one user, but was modified by another user, and we would like to know who modified the PO. In Smartlist and POP tables, we can only find "User to Enter", in other words, User who created the PO. But we cannot find a User who last modified field. We did not have Activity Tracking enabled at the time, so we'd like to know if there is another way to track who modified this PO, besides Activity Tracking. Please help...thank you No, ...

Last Modified time...
I have an excel spreadsheet that needs to keep track of the last time another spreadsheet was saved / modified. Is there a formula that does this? "Dennis" <anonymous@discussions.microsoft.com> schreef in bericht news:38d301c4c134$baf2c8f0$a301280a@phx.gbl... > I have an excel spreadsheet that needs to keep track of > the last time another spreadsheet was saved / modified. > Is there a formula that does this? > Public Function trackFile(strFile As String) As Date Dim fso, fs, d As Date Set fso = CreateObject("Scripting.FileSystemObject") ...

how to find the last modified user?
Is it possible in CRM to determine who last modified/altered a particular record (of any entity)? Esp, who last modified the details of a particular contact? Thanx There is an example in the SDK of building an "Auditing" module for the CRM admin which would give views of who changed which records, etc. http://msdn.microsoft.com/library/en-us/dnmbscrm1_2/html/mbs_crmauditing.asp Matt Wittemann http://icu-mscrm.blogspot.com "Simon" wrote: > Is it possible in CRM to determine who last modified/altered a particular > record (of any entity)? > Esp, who last mod...

How do I save the date a record was last modified in Access 2003
How do I save the date a record was last modified in Access 2003? I know a little about Access databases, and need help with a database I designed. Any comment will be appreciated. Anne Use the BeforeUpdate event of the *form* where the records are edited, e.g.: Private Sub Form_BeforeUpdate(Cancel As Integer) Me.[WhateverYourFieldIsCalled] = Now() End Sub -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Anne" <Anne@d...

How do I archive based on sent/ received date not "last modified" date
We have combined our group's e-mails into one account that we all access. Now there is a whole directory tree of projects that we all work on... and each of us can see the most current e- mails in any project regardless of who wrote them. However, when we try to archive them, they are archived based on the "last modified date". For most e-mails, this is when we moved them into the project folder (i.e. last week when we created the group e-mail) even though we may have received the e-mail months or years ago. Is there anyway to archive based on the sent or received dat...

Date last modified for a field
I have a field "EETitle". I need to track the last date updated. How would I do that? Thanks in advance. jhicsupt, You'll need to add a Date/Time field to your table, (ex. EETitleDOLE). Use the AfterUpdate event of EETitle to trigger the code... Me.EETitle = Now() -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job that you love... and you'll never work a day in your life." "jhicsupt" <jhicsupt@discussions.microsoft.com> wrote in message news:B6105B51-91C3...

Date last modified cell
Hi guys, I am wondering if there is a function or vba script in excel that can show the date my .xls fle was last modified? So i need it to only change when any cells are changed with in the workbook but if the workbook is opened and just viewed at, the date will not change. Thanks for the help. from Excel Help DateLastModified Property Description Returns the date and time that the specified file or folder was last modified. Read-only. Syntax object.DateLastModified The object is always a File or Folder object. Remarks The following code illustrates the use of the DateLastModif...

Determine who last modified an Excel file????
We work in a networked environment and there are only 2 users who have access to our Excel file. These 2 users never work the same shift, so therefore only one user will be modifying this file at any time. My question is, is there any VBA code to determine who was the last user to modify/save an Excel 2007 file?? Sometimes I would like to see who was the last user to edit this file. Thank you! i usually just create a hidden sheet. then in the thisworkbook before close module, add worksheets("Hidden Sheet name").range("A1").value =environ("Username...

Last ModifiedDate -15 days
I have a function which does the following: 1. Remove .xls from the file and use reminder as a wild card by additing * at both the ends 2. Query the directory (source path) for the wild card 3. Check the data stamp on each file matching the card 4. Pick up the latest file I now need the function to only process a file the last modified date is less than 15days old. How can I do this? Easy question I am sure but I cannot think of the answer. Many thanks SteveH when you get the fie date check that like If File_Date > Date -15 Then ... -- HTH Bob Phillips (replace somew...

Is there a way to find when was a DTS Pacakge last modified
1. I am still on SQL Server 2000. Is there a way some sql query e.g. on tables in msdb database), so I can find when was last time various packages were modified. I only see create date for pacakages in Entterprise Manager or in msdb sysdtspacakges I know in Enterprise Manager one can go to each DTS package one by one look at its various versions and perhaps try to figure this out, but I am looking for a faster way. 2. Is there some sql query to find sql used inside transformation of various DTS pacakges. Going in Designer in EM and clicking on properties on arrows connec...

How do I set the view to default to last modified
I like my files to always be in last modified order with the most recently saved file at the top of the list. In Excel 2000 I set the order in the open window and from then on all files in all folders were in the correct order. Recently I changed to Excel 2003. Since then I have had problems. I change the vies as before but each time i close the application it reverts to sorting by name - in alphabetical order. Any suggections? ...

Time Date Last Modified
I am sharing a worksheet with another person and we both are updating it often throughout the day. I'd like to have a cell display the last time and date it was modified. Can this be done? Thanks, Diane Only by using VBA http://tinyurl.com/apq8b Regards, Peo Sjoblom "LDL" wrote: > I am sharing a worksheet with another person and we both are updating it > often throughout the day. I'd like to have a cell display the last time and > date it was modified. Can this be done? Thanks, Diane > > > Here is an UDF Function DocProps(prop As St...

Last Modified Date in Header
I have a spreadsheet where I want the date displayed in the header to be the date the speadsheet was last modified, not the current date. Current I change this date manually each time I edit the spreadsheet, but is there some way have it done automatically? Thanks. Deb "Deb" <anonymous@discussions.microsoft.com> wrote in news:151201c3df81 $9f1d2b10$a601280a@phx.gbl: > I have a spreadsheet where I want the date displayed in > the header to be the date the speadsheet was last > modified, not the current date. > > Current I change this date manually each...

FRx
The auditors have asked to get data for when the last date an Frx report was modified, and we could not comply, as it is not stored in FRx. Microsoft Business Solutions Support Incident Number 8518345 : Last modified date in FRx Unfortunately, there is no way to do this within the front-end of FRx. I advised that it looks like there is a "Last Modified" value that is stored within the .f32 file, but I do not know if this value is accurate. I advised that there is no way to grab this value from within FRx. I advised that you may want to enter a suggestion to add this as a...

Last modified Date for an Access file
What is the vba command that yields the date when THE CODE for an access file was modifed for the last time? Can anybody post an example? Perhaps someone will say otherwise, but to my knowledge this cannot be done. You can determine the last modified time of the mdb file itself, but when someone edited the vba code... I do not believe this is possible. Why? What is the end goal? Perhaps we can guide you in another way. -- Hope this helps, Daniel Pineault http://www.cardaconsultants.com/ For Access Tips and Examples: http://www.devhut.net Please rate this post using the...