Add Date & Time Stamp to File Name When Saved

MikeeRDX

Board Regular
Joined
Feb 16, 2014
Messages
98
Hi,

I would like to have a date and time stamp as part of my file name whenever I click on a button to save my worksheet as a backup copy. I know how to do it with just a date, but I also would like to add a time stamp as well since the worksheet can be saved multiple times throughout the day. I would like to have a date & time stamp to save any updates as a file separate file. Is this possible and if so, how would you write the VBA code?

Thank you everyone for your help.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows
How are you doing the backup copy now ?
With a macro ?
If so, post what code you already have.
If not, maybe this

Code:
Sub SaveIt()
Dim dt As String, wbNam As String
wbNam = ThisWorkbook.Name
dt = Format(Date, "yyy_mm_dd_hh_mm")
ActiveWorkbook.SaveAs Filename:=wbNam & " " & dt & ".xlsm"
End Sub
 

MikeeRDX

Board Regular
Joined
Feb 16, 2014
Messages
98
Hi,

Thanks for your help. I used your code but this is what the file ends up saving as : WbName_09_16_2014_00_00_00.xlsm

The code I've been using is this:

Sub SaveMeDaily()
myDateStamp = Format(Date, "mmddyyyy")
FilePath = ThisWorkbook.Path & "/" & myDateStamp & ".xlsm"
ThisWorkbook.SaveCopyAs (FilePath)

End Sub
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Oops typo

Code:
Sub SaveMeDaily()
wbNam = ThisWorkbook.Name
myDateStamp = Format(Now(), "mmddyyyyhhmmss")
FilePath = ThisWorkbook.Path & "\" & wbNam & " " & myDateStamp & ".xlsm"
ThisWorkbook.SaveAs (FilePath)

End Sub
 
Last edited:

MikeeRDX

Board Regular
Joined
Feb 16, 2014
Messages
98
Thank you for your help! It worked but the time is in military: 18 33. Anyway we can make that 6:33PM?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
19,267
Office Version
  1. 2013
Platform
  1. Windows
Try
Code:
Sub SaveMeDaily()
wbNam = ThisWorkbook.Name
myDateStamp = Format(Now(), "d/mm/yyyy[$-409]h:mm:ss AM/PM")
FilePath = ThisWorkbook.Path & "\" & wbNam & " " & myDateStamp & ".xlsm"
ThisWorkbook.SaveAs (FilePath)

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,159
Messages
5,527,149
Members
409,749
Latest member
esmarques

This Week's Hot Topics

Top