Date Formula

XLerator · May 25th, 2002, 10:23 PM

Is there way I can write code that will allow me to save a file with yesterday's date?----ie

May 26, 2002.xls--where the date would change daily to yesterday....

Joe Was · May 25th, 2002, 11:42 PM

This code is automatic, you can convert it by changing the name to:

Sub SYDate()

and saving it to a module or sheet module!

P.S. If you convert it to a manual code, attach it to a Form-Button or a hot-key!

Hope this helps. It is tested and works, see notes in code below! The automatic code below saves the file to the current folder (Path-Directory) so wherever you opened the file from is where your file-date-name will be saved. You can modify the code to include a different path. Search this board for "Save" to see the code you will need to add. You should check the key-word search before you post as your question has been answered before and the answer could have been found by you with a simple search. At the top of the board is the word "Search" just click it and add a key-word. For your problem the trick was "Excel will automatically adjust a date function based upon any math you add! In this case I added -1 to the date formula. As a sheet formula try this =Today() in cell A1 and =Today()-1 in cell B1 and you will see that Excel automatically adjusted the date. JSW

Private Sub Workbook_Open()
'This code saves this workbook with a date attached to the file name.
'This code gos in the "ThisWorkbook" module!
Dim MyDate
Dim MyMonth
MyDate = Date
MyMonth = Month(MyDate)
'The code below saves the file with todays date amended to the end of the file name.
'Note: Hard-code the file name below, end file name with a -.
'ThisWorkbook.SaveAs Filename:="YourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date)

'This code saves the file with yesterdays date amended to the file name.
'Note: Hard-code the file name below, end file name with a -.
ThisWorkbook.SaveAs Filename:="HardCodeYourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) - 1 & "-" & Year(Date)
End Sub

[ This Message was edited by: Joe Was on 2002-05-25 22:45 ]

[ This Message was edited by: Joe Was on 2002-05-25 22:57 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 25th, 2002, 10:23 PM	#1
XLerator New Member Join Date: Apr 2002 Posts: 21	Is there way I can write code that will allow me to save a file with yesterday's date?----ie May 26, 2002.xls--where the date would change daily to yesterday....

May 25th, 2002, 11:42 PM	#2
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	This code is automatic, you can convert it by changing the name to: Sub SYDate() and saving it to a module or sheet module! P.S. If you convert it to a manual code, attach it to a Form-Button or a hot-key! Hope this helps. It is tested and works, see notes in code below! The automatic code below saves the file to the current folder (Path-Directory) so wherever you opened the file from is where your file-date-name will be saved. You can modify the code to include a different path. Search this board for "Save" to see the code you will need to add. You should check the key-word search before you post as your question has been answered before and the answer could have been found by you with a simple search. At the top of the board is the word "Search" just click it and add a key-word. For your problem the trick was "Excel will automatically adjust a date function based upon any math you add! In this case I added -1 to the date formula. As a sheet formula try this =Today() in cell A1 and =Today()-1 in cell B1 and you will see that Excel automatically adjusted the date. JSW Private Sub Workbook_Open() 'This code saves this workbook with a date attached to the file name. 'This code gos in the "ThisWorkbook" module! Dim MyDate Dim MyMonth MyDate = Date MyMonth = Month(MyDate) 'The code below saves the file with todays date amended to the end of the file name. 'Note: Hard-code the file name below, end file name with a -. 'ThisWorkbook.SaveAs Filename:="YourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) & "-" & Year(Date) 'This code saves the file with yesterdays date amended to the file name. 'Note: Hard-code the file name below, end file name with a -. ThisWorkbook.SaveAs Filename:="HardCodeYourFileNameHere-" & MonthName(MyMonth) & "-" & Day(Date) - 1 & "-" & Year(Date) End Sub [ This Message was edited by: Joe Was on 2002-05-25 22:45 ] [ This Message was edited by: Joe Was on 2002-05-25 22:57 ]