Solved How to add a date to MS Excel files.

Dell / Inspiron 17r
July 18, 2020 at 06:53:42
Specs: windows 10, 2.3/8
I have an Excel file that is named Track.xlsx that is kept on the desktop. Each time I update it, I manually add the current date so it is now Track_071820.xlsx. Is there a way that the current date will automatically appear in the file name each time the file is updated?

Thank you.
Brian W


See More: How to add a date to MS Excel files.


#1
July 18, 2020 at 07:28:36
Interesting question, and something I’ve occasionally wondered about too.

Dun a wee trawl around the duck pond and found these links:

http://dailydoseofexcel.com/archive...

https://www.extendoffice.com/docume...

https://dedicatedexcel.com/how-to-s...

https://stackoverflow.com/questions...

I’m no guru or even close to it re’ Excel, so there may be other methods to to the same end.

There are at least Three Excel gurus/geniuses here; and they may well prefer other solutions too. So hang about, pending their input?


Reply ↓  Report •

#2
July 18, 2020 at 19:26:02
Any of those VBA solutions should work fine. The one thing I might add is that you could use the BeforeClose event to save the file.

Place the code that adds the date to the file name inside the BeforeClose event and all you'll have to do is close the file to have it saved with the date. No need to actually run the Sub's that are suggested by those links. The BeforeClose event can automatically save it for you.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#3
July 20, 2020 at 03:06:52
✔ Best Answer
If your file is named as Track.xlsx and it's always in your Desktop , you can try with this batch file : Just copy and paste this code below in your notepad or notepad++ and save it as Rename_Date_XLSX.bat and execute by double click !

@echo off
Color 0A & Mode 80,8
Title Get Date and Time and rename file with date 
Set "Desktop=%USERPROFILE%\Desktop"
Set "File2Rename=%Desktop%\Track.xlsx"
@for %%a in ("%File2Rename%") do ( 
	set "FileName=%%~na"
	set "Ext=%%~xa"
)

echo(
echo( Current Name = "%FileName%%Ext%"
Call :Get_Date_Time 
echo( Date : %Year%-%Month%-%Day%
echo( Time : %Hour%:%Min%:%Sec%
Set "NEW_DATE_NAME=%Month%%Day%%Year%"
echo( NEW_DATE_NAME = "%FileName%%NEW_DATE_NAME%%Ext%"
If Exist "%File2Rename%" (
	REN "%File2Rename%" "%FileName%%NEW_DATE_NAME%%Ext%"
) Else (
	Color 0C
	echo( The file "%File2Rename%" does not exist - Please check it!
)
Timeout /T 5 /NoBreak>nul & Exit
::--------------------------------------------------------------------------------------------
:Get_Date_Time
for /f "skip=1" %%x in ('wmic os get localdatetime') do if not defined MyDate set "MyDate=%%x"
set "Year=%MyDate:~0,4%"
set "Month=%MyDate:~4,2%"
set "Day=%MyDate:~6,2%"
set "Hour=%MyDate:~8,2%"
set "Min=%MyDate:~10,2%"
set "Sec=%MyDate:~12,2%
exit /b
::--------------------------------------------------------------------------------------------


Reply ↓  Report •

Related Solutions

#4
July 20, 2020 at 06:19:56
trvir,

Thank you for your suggestions. I have never used VBA but will give it a try.

Brian W


Reply ↓  Report •

#5
July 20, 2020 at 06:20:45
DerbyDad03,

Thank you for your suggestions. I'll give it a try.

Brian W


Reply ↓  Report •

#6
July 20, 2020 at 06:21:47
Hackoo,

Thank you for your suggestion. I'll give it a try and get back to you.

Brian W


Reply ↓  Report •

#7
July 20, 2020 at 09:10:08
Hackoo,

Thank you for your suggestion. As I have never tried VBA, I tried your coding and it worked exactly as I needed.

Thanks again.
Brian W


Reply ↓  Report •

#8
July 31, 2020 at 12:12:42
Hackoo,

The batch file is not working correctly. I get the following"

Current Name = "TMBicycle.xlsx"
Date : 2020-07-31
Time : 15:04:37
NEW_DATE_NAME = "TMBicycle07312020.xlsx"
The file "C:\Users\DrWac\Desktop\TMBicycle.xlsx" does not exist - Please check it!

The first time I updated the file its name was "TMBicycle.xlsx" and it updated to "TMBicycle07252020. The second time I tried to update I got the above error message. I don't know a whole lot about batch files but shouldn't the "Current Name" now be TMBicycle07252020.xlsx?

Thank you.
Brian W


Reply ↓  Report •

Ask Question