# Bat file to copy xls and add time/date



## ecellis (Jul 4, 2011)

Hi,
I am trying to create a simple bat file that will copy all the excel files in a folder into another folder but add the time and date to the name. I am running Windows 7 if that matters. Here is basically what I am looking for:

example1.xls, example2.xls, example3.xls are the original files. I want to copy them into a backup directory that will already contain hundreds of backed up xls files that need to stay untouched. When they are being copied, I want the time (hhmm) and date (mmddyy) appended to the end of the filename.
I want the files to turn out like this: [email protected], [email protected], etc.

I started out with this: copy *.xls %date:~4,2%%date:~7,2%%date:~10,4%*.xls but it was over writing the filename so I didn't even try the time part. It has been a long time since I have worked with DOS and I forgot most of it. I still will use the occasional BAT file to automate something.

I would prefer it to stay simple so I can modify it as needed. I don't care if the time needs a 0 for 2:00(0200) or if it is military time. I do want the date to stay MMDDYY (1st choice) or MMDDYYYY.

Thank you for any help you provide!


----------



## Squashman (Apr 4, 2003)

Can you show me the output of your Date and Time variables from the cmd prompt.


```
H:\>echo %time%
 8:00:01.92

H:\>echo %date%
Tue 11/15/11

H:\>
```


----------



## ecellis (Jul 4, 2011)

Hi Squashman,
Thanks for replying! Here is the answers to your questions.

Time is:
0:07:45.69 (12:07 AM)

Date is :
Thu 11/17/2011

Thanks again!


----------



## Squashman (Apr 4, 2003)

You should be able to do something like this.

```
FOR %%A in (*.xls) do copy "%%~A" "%date:~4,2%%date:~7,2%%date:~10,4%_%%~A"
```


----------



## ecellis (Jul 4, 2011)

Hi Squashman. Thanks for the reply. I've been out this weekend and just got back. I tried the code above but I get an error saying that %%A was unexpected at this time. Any ideas?

Thanks again!


----------



## Squashman (Apr 4, 2003)

Did you put that code into a batch file or did you run it from the command prompt?


----------



## ecellis (Jul 4, 2011)

Hi Squashman,
I think I figured it out. Here is what I ended up with so others can save time:

@Echo Off
@For /F "tokens=1,2,3,4 delims=/ " %%A in ('Date /t') do @( 
Set Day=%%A
Set Month=%%B
Set Year=%%C
Set Year=%%D
Set All=%%A%%B%%C%%D
)
@For /F "tokens=1,2,3 delims=: " %%A in ('Time /t') do @( 
Set Hour=%%A
Set Min=%%B
Set Sec=%%C
Set Allm=%%A%%B%%C
)

@For %%a in ("*.xls") do copy %%a "%%~na_%All%_%Allm%.xls"

I kind of combined a couple of pieces I saw in other posts into 1 and edited the last line to include both. It seems to work. I will probably strip out the day. I have never used the FOR /f before but it seems like it can be pretty useful! It's been a while since the good old DOS days and my memories are a bit mushy.

One thing I don't really get is why the seconds don't show up in the file name. I don't want them, but it looks like they are being set. Is it because the time /t command does not show seconds?

Again, thank for replying and the help! I really do appreciate it!


----------



## ecellis (Jul 4, 2011)

Squashman said:


> Did you put that code into a batch file or did you run it from the command prompt?


Duh... I forgot about the %% vs % difference when a bat file vs straight command. 

Banging head on the wall

Your code wins for it's simplicity! I modified it to:
FOR %%A in (*.xls) do copy "%%~A" "%date:~4,2%-%date:~7,2%-%date:~10,4%@%time:~0,2%%time:~3,2%_%%~A"

The file name ends up looking like this: [email protected]_example.xls (ack the message board made it a link but you get the idea - there is an _ betwen 1935 and example)

That way it has the time too. It shows the time in 24 hour format but I guess that will work. Is there a way to bring it to 12h format?

Thanks!


----------

