Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week Numbers
I am trying to get column headers in the format of YYWW
(ie. 0352, 0401, 0402 etc.) from the current date then 52 weeks/columns into the future. I thought I had cracked it using the not very neat solution of:- Range("A1") = Right(DatePart("yyyy", Date), 2) & DatePart ("ww", Date) then using a custom list to autofill the other columns. 0352 worked fine then 0401 came out as 041 and I'm back to scatching my head again. Any ideas? Graham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Week Numbers
On Wed, 7 Jan 2004 07:14:17 -0800, "Graham"
wrote: I am trying to get column headers in the format of YYWW (ie. 0352, 0401, 0402 etc.) from the current date then 52 weeks/columns into the future. I thought I had cracked it using the not very neat solution of:- Range("A1") = Right(DatePart("yyyy", Date), 2) & DatePart ("ww", Date) then using a custom list to autofill the other columns. 0352 worked fine then 0401 came out as 041 and I'm back to scatching my head again. Any ideas? Graham Well, your problem is the formatting of the results. But there is an interesting additional problem in determining the week number. DatePart, without any arguments, assumes the first week of the year is the week in which Jan 1 appears. So, for example, 12/29/04 would be the 53rd week of 2004 (or 0453 for your column header), but seven days later would be the second week of 2005 (or 0502). Might I suggest the following, which returns the headers as a formatted number and includes the sequence 0453 - 0502 ======================= Sub ColHdr() Dim Yr As Integer, Wnum As Integer Dim CurDate As Date Dim ColHdr As String Const NumColHdrs As Integer = 60 Dim i As Integer CurDate = Date For i = 0 To NumColHdrs Yr = DatePart("yyyy", CurDate) Mod 100 Wnum = DatePart("ww", CurDate) ColHdr = Yr * 100 + Wnum Cells(1, 1 + i).Value = ColHdr Cells(1, 1 + i).NumberFormat = "0000" CurDate = CurDate + 7 Next i End Sub ===================== If this is not suitable, consider what you want to do, and how you want to handle the "short weeks". --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
week numbers | Excel Discussion (Misc queries) | |||
Moving week numbers | Excel Worksheet Functions | |||
I need to =a set of numbers to a given week day.ie 6031 = Wed ect | Excel Discussion (Misc queries) | |||
Week Numbers | Excel Discussion (Misc queries) | |||
How do I use week numbers for sub-totalling? | Excel Discussion (Misc queries) |