# Excel Macro ? - Add Date/Time/Username **URL:** \ **Category:** Factual Questions\ **Created:** [July 27, 2016, 3:34pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033 "2016-07-27T15:34:23Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![Ace331](https://avatars.discourse-cdn.com/v4/letter/a/f9ae1b/32.png) [@Ace331](https://boards.straightdope.com/u/Ace331)\ **Post date:** [July 27, 2016, 3:34pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/1 "2016-07-27T15:34:23Z")
I am trying to create a macro that will allow me to show in Cell B1, the last date and time the file was saved and in Cell B2, the username of the last person that saved it. I am not familiar at all with writing macros and after a bit of googling I was able to put together the below. This pulls in the correct date, but not the correct time. Time shows as 12:00 AM. And I would still need to add in the username portion. If too much trouble, the username is not as important as being able to tell when the file was last updated. Any help would be greatly appreciated. Sub LastSaved() Dim sLMD As String ``` On Error Resume Next sLMD = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time") If Err = 440 Then Err = 0 sLMD = "Not Set" End If sLMD = Format(sLMD, "dd/mm/yy h:mm") Range("B1").Value = "Last Saved: " & sLMD ``` End Sub EDIT: I know this info can be seen when viewing the filename in the folder it is in, but I highly doubt anyone in my office ever pays attantion to that. Will be more visible to have at the top of the file once opened. ---
**Author:** ![Skammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/skammer/32/143_2.png) [@Skammer](https://boards.straightdope.com/u/Skammer)\ **Post date:** [July 27, 2016, 4:40pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/2 "2016-07-27T16:40:46Z")
What version of Excel are you using? ---
**Author:** ![Ace331](https://avatars.discourse-cdn.com/v4/letter/a/f9ae1b/32.png) [@Ace331](https://boards.straightdope.com/u/Ace331)\ **Post date:** [July 27, 2016, 4:47pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/3 "2016-07-27T16:47:16Z")
2007 ---
**Author:** ![Skammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/skammer/32/143_2.png) [@Skammer](https://boards.straightdope.com/u/Skammer)\ **Post date:** [July 27, 2016, 5:02pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/4 "2016-07-27T17:02:27Z")
This worked for me in 2010. Sorry I don’t have 2007 to try it out in: ```auto Sub LastSaved() Dim sLMD As String On Error Resume Next sLMD = ActiveWorkbook.BuiltinDocumentProperties(12).Value sLAD = ActiveWorkbook.BuiltinDocumentProperties(7).Value If Err <> 0 Then Err = 0 sLMD = "Not Set" sLAD = "Not Set" End If sLMD = Format(sLMD, "dd/mm/yy h:mm") Range("B1").Value = "Last Saved: " & sLMD Range("B2").Value = "User: " & sLAD End Sub ``` I just noticed I didn’t add “DIM sLAD as STRING”. Apparently 2010 doesn’t need it but 2007 might. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 27, 2016, 5:05pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/5 "2016-07-27T17:05:19Z")
I’d recommend a slightly different approach. Create a generic function for any document property, then use that function in the cell instead of using a macro to insert it. That way you can add the function to your Normal template and use it in any document. So the function would be this: ```auto Public Function DocProperty(propertyName) Application.Volatile DocProperty = ThisWorkbook.BuiltinDocumentProperties(propertyName) End Function ``` Then in cell B1 enter ```auto =DocProperty("Last save time") ``` I also recommend putting the text "Last Saved: " in a separate cell so you can simply format the cell with the date/time as you desire, but if you want it all in one cell you can use concatenation and the FORMAT function. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [July 27, 2016, 5:06pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/6 "2016-07-27T17:06:57Z")
> [@Skammer](#): > > I just noticed I didn’t add “DIM sLAD as STRING”. Apparently 2010 doesn’t need it but 2007 might. This is not related to the Excel version. If you have “Option Explicit” at the start of the VBA module, then you must first “dim” any variables you use. It’s good practice, IMO. ---
**Author:** ![Skammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/skammer/32/143_2.png) [@Skammer](https://boards.straightdope.com/u/Skammer)\ **Post date:** [July 27, 2016, 5:10pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/7 "2016-07-27T17:10:42Z")
Thanks Troutman. I’m self-taught so I’m sure I’ve missed a lot of ‘best practice’ lessons over the years. But that makes sense. ---
**Author:** ![CookingWithGas](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/cookingwithgas/32/485_2.png) [@CookingWithGas](https://boards.straightdope.com/u/CookingWithGas)\ **Post date:** [July 27, 2016, 6:48pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/8 "2016-07-27T18:48:41Z")
Another approach would be to use the Workbook\_BeforeSave event to put the current date and time and username in that cell every time it’s saved, rather than retrieving it after the fact. Your description mentions user name but none of the code in this thread addresses that and I don’t think you can retrieve that after the fact. ---
**Author:** ![Disgscen](https://avatars.discourse-cdn.com/v4/letter/d/f05b48/32.png) [@Disgscen](https://boards.straightdope.com/u/Disgscen)\ **Post date:** [July 27, 2016, 7:31pm UTC](https://boards.straightdope.com/t/excel-macro-add-date-time-username/761033/9 "2016-07-27T19:31:23Z")
To expand on CookingWithGas’s reply, place this in the ThisWorkbook code: Private Sub Workbook\_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Range(“B1”) = Format(Now(), “General Date”) Range(“B2”) = Application.UserName End Sub (Or if you must): Range(“B1”) = "Last Saved: " & Format(Now(), “General Date”)