Hello Matt,
Since the ISO 8601 week and the Gregorian calendar week are not identical, you will need to use a conversion function to return the correct ISO week.
From Chip Pearson's site http://www.cpearson.com/excel/WeekNumbers.aspx
Public Function IsoWeekNumber(InDate As Date) As Integer Dim D As Long D = DateSerial(Year(InDate - Weekday(InDate - 1) + 4), 1, 3) IsoWeekNumber = Int((InDate - D + Weekday(D) + 5) / 7) End Function
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hi Leith!
Thanks for the reply.
I guess I don't know exactly how to insert that into my code....or reference that Function in my ActiveWorkbook.SaveAs statement....
I put the new Public Function in Module 1 above the ActiveWorkbook.SaveAs Sub, but what do I change on my
Thanks again for your help!Format(Date, "WW", vbUseSystemDayOfWeek, vbUseSystem)
Hello Matt,
Copy Chip's code in a standard VBA Module. Add this line where you need it...
ActiveWorkbook.SaveAs Application.UserName & "_" & "FCST" & "_" & Format(Date, "MMDDYYYY") & "_" & "CW" & IsoWeekNumber(Date)
Thanks Leith! I was so close, just missed the last part of "(Date)".
Works great!
I will close the thread as Solved.
Cheers,
Matt
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
matt4003 
Register To Reply
Bookmarks