EDIT: This only works well if you have macros enabled by default. If they aren't, then the file date won't update when you open the file and you'll have to reinsert the function for it to work.
Ever find yourself needing to insert the file creation date or date last saved into your Excel worksheet? Well, me either, until I started a grad class in Advanced Business Applications (i.e. Excel and Access).
We were learning VBA in my last class, and one of my classmates asked if it's possible to call the file creation date and last save date for cell insertion. I had no idea, but my curiosity was piqued, so I came home after class and spent a few hours researching the possibility. I should probably also mention here that I really only know very basic VBA…
Anyway, after reading more code that I care to remember, the short answer is yes, but the long answer is that it’s not terribly easy. It’s also not very elegant, but it does what I want it to do.
I’ve written two user-defined functions (UDF) to call the document properties, and a macro to format the date and time. Here they are in all their glory:
Module to insert the date the file was created:
Function Created() As Date
Created = ActiveWorkbook.BuiltinDocumentProperties(11)
End Function
To insert the creation date, use the function =Created()
Module to insert the date the file was last saved:
Function ModDate() As Date
ModDate = ActiveWorkbook.BuiltinDocumentProperties(12)
End Function
To insert the modified date, use the function =ModDate()
Macro to format the dates to show as “January 29, 2010 6:18 PM”
Sub FormatDate()
Selection.NumberFormat = "mmmm dd, yyyy, h:mm AM/PM"
End Sub
Use the macro ‘FormatDate’ to easily format the cell to show date and time.
Be sure to give the module a different name than the function name. Strange things happen if you name both the same. These should be saved as a module in your personal macro workbook so that you can always easily find them. You can call the function from the other worksheet, or copy them to the file you are working in. I recommend copying them into the workbook in which you want to use them so that they will work for others if you share the file.
You will have to enable macros if given the option, but when enabled, the last saved date will update in your worksheet.
Hopefully this will save you some time should you ever find yourself needing to insert the file creation or modified date into an Excel spreadsheet.
~Dawn
