Here is what I need:

I need a dynamic batch file written to create all of Joe's timesheets for the year.

Sunday is the end of the pay period.

For example the Sunday in January 2019 is the 5th

The file name would be 20190105 the folder will be named c:\Timesheet\2019\01-January\

The complete file path would be --> c:\Timesheet\2019\01-January\20190105-timesheet.xlsx

You can use excel and concatenation to dynamically build the file name and file path for the batch file.

This should all be accomplished using two columns in excel one for the week ending date and one for the formula.

This same process will need to occur each week and include the first pay period of 2020.

The attached excel file is the file you will copy to create each weekly.

The excel file should be smart enough to dynamically build the file by entering 1/1/2019.

When 1/1/2020 is entered it should create the same script for next years data.

Let me know if you have any questions.

Due date is Tuesday 1/29 at 11:00 AM that should be ample time to complete this task



You may need to use two batch files one to create the folders and one to copy the file.


**Here is what I have for TS.xlsx but trying to figure out how to get linked to master timecard. I save TS as batch file.

01-January ="if not exist "&"""C:\Timsheets\"&A1&"\"" mkdir C:\Timsheets\"&A1
02-February ="if not exist "&"""C:\Timsheets\"&A2&"\"" mkdir C:\Timsheets\"&A2
03-March ="if not exist "&"""C:\Timsheets\"&A3&"\"" mkdir C:\Timsheets\"&A3
04-April ="if not exist "&"""C:\Timsheets\"&A4&"\"" mkdir C:\Timsheets\"&A4
05-May ="if not exist "&"""C:\Timsheets\"&A5&"\"" mkdir C:\Timsheets\"&A5
06-June ="if not exist "&"""C:\Timsheets\"&A6&"\"" mkdir C:\Timsheets\"&A6
07-July ="if not exist "&"""C:\Timsheets\"&A7&"\"" mkdir C:\Timsheets\"&A7
08-August ="if not exist "&"""C:\Timsheets\"&A8&"\"" mkdir C:\Timsheets\"&A8
09-September ="if not exist "&"""C:\Timsheets\"&A9&"\"" mkdir C:\Timsheets\"&A9
10-October ="if not exist "&"""C:\Timsheets\"&A10&"\"" mkdir C:\Timsheets\"&A10
11-November ="if not exist "&"""C:\Timsheets\"&A11&"\"" mkdir C:\Timsheets\"&A11
12-December ="if not exist "&"""C:\Timsheets\"&A12&"\"" mkdir C:\Timsheets\"&A12
43471 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A13),"00")&"-"&TEXT(A13,"mmmm")&"\"&TEXT(YEAR(A13), "0000")&TEXT(MONTH(A3), "00")&TEXT(DAY(A13), "00")&"-Timesheet.xlsx"
=A13+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A14),"00")&"-"&TEXT(A14,"mmmm")&"\"&TEXT(YEAR(A14), "0000")&TEXT(MONTH(A4), "00")&TEXT(DAY(A14), "00")&"-Timesheet.xlsx"
=A14+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A15),"00")&"-"&TEXT(A15,"mmmm")&"\"&TEXT(YEAR(A15), "0000")&TEXT(MONTH(A5), "00")&TEXT(DAY(A15), "00")&"-Timesheet.xlsx"
=A15+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A16),"00")&"-"&TEXT(A16,"mmmm")&"\"&TEXT(YEAR(A16), "0000")&TEXT(MONTH(A6), "00")&TEXT(DAY(A16), "00")&"-Timesheet.xlsx"
=A16+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A17),"00")&"-"&TEXT(A17,"mmmm")&"\"&TEXT(YEAR(A17), "0000")&TEXT(MONTH(A7), "00")&TEXT(DAY(A17), "00")&"-Timesheet.xlsx"
=A17+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A18),"00")&"-"&TEXT(A18,"mmmm")&"\"&TEXT(YEAR(A18), "0000")&TEXT(MONTH(A8), "00")&TEXT(DAY(A18), "00")&"-Timesheet.xlsx"
=A18+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A19),"00")&"-"&TEXT(A19,"mmmm")&"\"&TEXT(YEAR(A19), "0000")&TEXT(MONTH(A9), "00")&TEXT(DAY(A19), "00")&"-Timesheet.xlsx"
=A19+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A20),"00")&"-"&TEXT(A20,"mmmm")&"\"&TEXT(YEAR(A20), "0000")&TEXT(MONTH(A10), "00")&TEXT(DAY(A20), "00")&"-Timesheet.xlsx"
=A20+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A21),"00")&"-"&TEXT(A21,"mmmm")&"\"&TEXT(YEAR(A21), "0000")&TEXT(MONTH(A11), "00")&TEXT(DAY(A21), "00")&"-Timesheet.xlsx"
=A21+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A22),"00")&"-"&TEXT(A22,"mmmm")&"\"&TEXT(YEAR(A22), "0000")&TEXT(MONTH(A12), "00")&TEXT(DAY(A22), "00")&"-Timesheet.xlsx"
=A22+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A23),"00")&"-"&TEXT(A23,"mmmm")&"\"&TEXT(YEAR(A23), "0000")&TEXT(MONTH(A13), "00")&TEXT(DAY(A23), "00")&"-Timesheet.xlsx"
=A23+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A24),"00")&"-"&TEXT(A24,"mmmm")&"\"&TEXT(YEAR(A24), "0000")&TEXT(MONTH(A14), "00")&TEXT(DAY(A24), "00")&"-Timesheet.xlsx"
=A24+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A25),"00")&"-"&TEXT(A25,"mmmm")&"\"&TEXT(YEAR(A25), "0000")&TEXT(MONTH(A15), "00")&TEXT(DAY(A25), "00")&"-Timesheet.xlsx"
=A25+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A26),"00")&"-"&TEXT(A26,"mmmm")&"\"&TEXT(YEAR(A26), "0000")&TEXT(MONTH(A16), "00")&TEXT(DAY(A26), "00")&"-Timesheet.xlsx"
=A26+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A27),"00")&"-"&TEXT(A27,"mmmm")&"\"&TEXT(YEAR(A27), "0000")&TEXT(MONTH(A17), "00")&TEXT(DAY(A27), "00")&"-Timesheet.xlsx"
=A27+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A28),"00")&"-"&TEXT(A28,"mmmm")&"\"&TEXT(YEAR(A28), "0000")&TEXT(MONTH(A18), "00")&TEXT(DAY(A28), "00")&"-Timesheet.xlsx"
=A28+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A29),"00")&"-"&TEXT(A29,"mmmm")&"\"&TEXT(YEAR(A29), "0000")&TEXT(MONTH(A19), "00")&TEXT(DAY(A29), "00")&"-Timesheet.xlsx"
=A29+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A30),"00")&"-"&TEXT(A30,"mmmm")&"\"&TEXT(YEAR(A30), "0000")&TEXT(MONTH(A20), "00")&TEXT(DAY(A30), "00")&"-Timesheet.xlsx"
=A30+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A31),"00")&"-"&TEXT(A31,"mmmm")&"\"&TEXT(YEAR(A31), "0000")&TEXT(MONTH(A21), "00")&TEXT(DAY(A31), "00")&"-Timesheet.xlsx"
=A31+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A32),"00")&"-"&TEXT(A32,"mmmm")&"\"&TEXT(YEAR(A32), "0000")&TEXT(MONTH(A22), "00")&TEXT(DAY(A32), "00")&"-Timesheet.xlsx"
=A32+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A33),"00")&"-"&TEXT(A33,"mmmm")&"\"&TEXT(YEAR(A33), "0000")&TEXT(MONTH(A23), "00")&TEXT(DAY(A33), "00")&"-Timesheet.xlsx"
=A33+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A34),"00")&"-"&TEXT(A34,"mmmm")&"\"&TEXT(YEAR(A34), "0000")&TEXT(MONTH(A24), "00")&TEXT(DAY(A34), "00")&"-Timesheet.xlsx"
=A34+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A35),"00")&"-"&TEXT(A35,"mmmm")&"\"&TEXT(YEAR(A35), "0000")&TEXT(MONTH(A25), "00")&TEXT(DAY(A35), "00")&"-Timesheet.xlsx"
=A35+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A36),"00")&"-"&TEXT(A36,"mmmm")&"\"&TEXT(YEAR(A36), "0000")&TEXT(MONTH(A26), "00")&TEXT(DAY(A36), "00")&"-Timesheet.xlsx"
=A36+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A37),"00")&"-"&TEXT(A37,"mmmm")&"\"&TEXT(YEAR(A37), "0000")&TEXT(MONTH(A27), "00")&TEXT(DAY(A37), "00")&"-Timesheet.xlsx"
=A37+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A38),"00")&"-"&TEXT(A38,"mmmm")&"\"&TEXT(YEAR(A38), "0000")&TEXT(MONTH(A28), "00")&TEXT(DAY(A38), "00")&"-Timesheet.xlsx"
=A38+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A39),"00")&"-"&TEXT(A39,"mmmm")&"\"&TEXT(YEAR(A39), "0000")&TEXT(MONTH(A29), "00")&TEXT(DAY(A39), "00")&"-Timesheet.xlsx"
=A39+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A40),"00")&"-"&TEXT(A40,"mmmm")&"\"&TEXT(YEAR(A40), "0000")&TEXT(MONTH(A30), "00")&TEXT(DAY(A40), "00")&"-Timesheet.xlsx"
=A40+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A41),"00")&"-"&TEXT(A41,"mmmm")&"\"&TEXT(YEAR(A41), "0000")&TEXT(MONTH(A31), "00")&TEXT(DAY(A41), "00")&"-Timesheet.xlsx"
=A41+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A42),"00")&"-"&TEXT(A42,"mmmm")&"\"&TEXT(YEAR(A42), "0000")&TEXT(MONTH(A32), "00")&TEXT(DAY(A42), "00")&"-Timesheet.xlsx"
=A42+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A43),"00")&"-"&TEXT(A43,"mmmm")&"\"&TEXT(YEAR(A43), "0000")&TEXT(MONTH(A33), "00")&TEXT(DAY(A43), "00")&"-Timesheet.xlsx"
=A43+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A44),"00")&"-"&TEXT(A44,"mmmm")&"\"&TEXT(YEAR(A44), "0000")&TEXT(MONTH(A34), "00")&TEXT(DAY(A44), "00")&"-Timesheet.xlsx"
=A44+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A45),"00")&"-"&TEXT(A45,"mmmm")&"\"&TEXT(YEAR(A45), "0000")&TEXT(MONTH(A35), "00")&TEXT(DAY(A45), "00")&"-Timesheet.xlsx"
=A45+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A46),"00")&"-"&TEXT(A46,"mmmm")&"\"&TEXT(YEAR(A46), "0000")&TEXT(MONTH(A36), "00")&TEXT(DAY(A46), "00")&"-Timesheet.xlsx"
=A46+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A47),"00")&"-"&TEXT(A47,"mmmm")&"\"&TEXT(YEAR(A47), "0000")&TEXT(MONTH(A37), "00")&TEXT(DAY(A47), "00")&"-Timesheet.xlsx"
=A47+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A48),"00")&"-"&TEXT(A48,"mmmm")&"\"&TEXT(YEAR(A48), "0000")&TEXT(MONTH(A38), "00")&TEXT(DAY(A48), "00")&"-Timesheet.xlsx"
=A48+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A49),"00")&"-"&TEXT(A49,"mmmm")&"\"&TEXT(YEAR(A49), "0000")&TEXT(MONTH(A39), "00")&TEXT(DAY(A49), "00")&"-Timesheet.xlsx"
=A49+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A50),"00")&"-"&TEXT(A50,"mmmm")&"\"&TEXT(YEAR(A50), "0000")&TEXT(MONTH(A40), "00")&TEXT(DAY(A50), "00")&"-Timesheet.xlsx"
=A50+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A51),"00")&"-"&TEXT(A51,"mmmm")&"\"&TEXT(YEAR(A51), "0000")&TEXT(MONTH(A41), "00")&TEXT(DAY(A51), "00")&"-Timesheet.xlsx"
=A51+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A52),"00")&"-"&TEXT(A52,"mmmm")&"\"&TEXT(YEAR(A52), "0000")&TEXT(MONTH(A42), "00")&TEXT(DAY(A52), "00")&"-Timesheet.xlsx"
=A52+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A53),"00")&"-"&TEXT(A53,"mmmm")&"\"&TEXT(YEAR(A53), "0000")&TEXT(MONTH(A43), "00")&TEXT(DAY(A53), "00")&"-Timesheet.xlsx"
=A53+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A54),"00")&"-"&TEXT(A54,"mmmm")&"\"&TEXT(YEAR(A54), "0000")&TEXT(MONTH(A44), "00")&TEXT(DAY(A54), "00")&"-Timesheet.xlsx"
=A54+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A55),"00")&"-"&TEXT(A55,"mmmm")&"\"&TEXT(YEAR(A55), "0000")&TEXT(MONTH(A45), "00")&TEXT(DAY(A55), "00")&"-Timesheet.xlsx"
=A55+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A56),"00")&"-"&TEXT(A56,"mmmm")&"\"&TEXT(YEAR(A56), "0000")&TEXT(MONTH(A46), "00")&TEXT(DAY(A56), "00")&"-Timesheet.xlsx"
=A56+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A57),"00")&"-"&TEXT(A57,"mmmm")&"\"&TEXT(YEAR(A57), "0000")&TEXT(MONTH(A47), "00")&TEXT(DAY(A57), "00")&"-Timesheet.xlsx"
=A57+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A58),"00")&"-"&TEXT(A58,"mmmm")&"\"&TEXT(YEAR(A58), "0000")&TEXT(MONTH(A48), "00")&TEXT(DAY(A58), "00")&"-Timesheet.xlsx"
=A58+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A59),"00")&"-"&TEXT(A59,"mmmm")&"\"&TEXT(YEAR(A59), "0000")&TEXT(MONTH(A49), "00")&TEXT(DAY(A59), "00")&"-Timesheet.xlsx"
=A59+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A60),"00")&"-"&TEXT(A60,"mmmm")&"\"&TEXT(YEAR(A60), "0000")&TEXT(MONTH(A50), "00")&TEXT(DAY(A60), "00")&"-Timesheet.xlsx"
=A60+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A61),"00")&"-"&TEXT(A61,"mmmm")&"\"&TEXT(YEAR(A61), "0000")&TEXT(MONTH(A51), "00")&TEXT(DAY(A61), "00")&"-Timesheet.xlsx"
=A61+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A62),"00")&"-"&TEXT(A62,"mmmm")&"\"&TEXT(YEAR(A62), "0000")&TEXT(MONTH(A52), "00")&TEXT(DAY(A62), "00")&"-Timesheet.xlsx"
=A62+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A63),"00")&"-"&TEXT(A63,"mmmm")&"\"&TEXT(YEAR(A63), "0000")&TEXT(MONTH(A53), "00")&TEXT(DAY(A63), "00")&"-Timesheet.xlsx"
=A63+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A64),"00")&"-"&TEXT(A64,"mmmm")&"\"&TEXT(YEAR(A64), "0000")&TEXT(MONTH(A54), "00")&TEXT(DAY(A64), "00")&"-Timesheet.xlsx"
=A64+7 ="Copy Master Time Sheet Form.xlsx c:\Timesheet\2019\"&TEXT(MONTH(A65),"00")&"-"&TEXT(A65,"mmmm")&"\"&TEXT(YEAR(A65), "0000")&TEXT(MONTH(A55), "00")&TEXT(DAY(A65), "00")&"-Timesheet.xlsx"