Week Numbers

  • Thread starter Thread starter Graham
  • Start date Start date
G

Graham

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
 
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
Click to expand...

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
 
Back
Top