Date Functions in Excel

[mark73181]

Hi guys, I have a question. I couldn’t find an answer on previous post
so I’m assuming it hasn’t been discussed before.

I have a weekly worksheet I do for a game me and my friends play
mainly to keep scores. We have a column for each day and we save th
files based on the week ending date.

My question is how I can have the Week Ending Cell change th
individual dates in each column based on the actual end of the wee
date. Then I need the file name to be automatically updated based o
the end of the week date as well. My filenames are in this forma
“GameWE_071406.xls”. The problem I am facing is that excel doesn’t see
to let me just type in 071406 as a date and keep it that way, i
converts it to 07/14/06 or changes it completely.

Is there a way to do this?

Any help would be appreciated.

Thanks a lot,
Mar

 

[raypayette]

Assuming your date is in cell B6, select another cell and format it as
text. Then insert this in the cell:
=DAY(B6)&MONTH(B6)&RIGHT(YEAR(B6),2)
You can then use this to rename the sheet.

 

[mark73181]

Thanks that worked good.

But is there any way to use that function in a file path? To creat
dynamic file names based on the date entered above?

If my date is 7/15/06 Entered in cell B6, I want it to later create th
part in bold below based on the 7/15/06 Date.

='C:\Files\[Game*071506*.xls]DailyWS'!$B$5

I am trying to refer to past weeks saved on other spreadsheets withi
the current excel sheet to compare two weeks.

Thanks,
Mar

 

[jb]

You might try:

nameFILE = Format(Sheets("Sheet Name").Range("B6").Value, "Game_" &
"mmddyy") _
& ".xls"

ActiveWorkbook.SaveCopyAs FileName:= _
"\\Lab_fsvr\LAB\Spreadsheets\Game\" & nameFILE