I just thought I might post this function I created to get what week
number it is if a businesses week starts on a Sunday.

I am a bit of a "hack" programmer and came up with this after days of
trial and error, and would love if any professional out there could let
me know if it is "hilarious" or "ingeniuos" or any improvements anyone
might have.

This works only for the Year 2000 and above (at least to 2100).

Here is the code

Function GetWeeksBeginningSunday()
Dim hold, holdYear, slashPosition, holdMonth, monthAndYearOnly,
holdDay, holdLen As String
Dim intweek, daysUpToCurrent, January, February, March, April, May,
June, July, August, September, October, November, December As Integer
Dim daysBeforeFebruary, daysBeforeMarch, daysBeforeApril,
daysBeforeMay, daysBeforeJune As Integer
Dim daysBeforeJuly, daysBeforeAugust, daysBeforeSeptember,
daysBeforeOctober, daysBeforeNovember, daysBeforeDecember As Integer
Dim decweek As Double
Dim daysLeftInStartofYearWeek, days, i, afterLeapyear As Integer
Dim tempDate As String


tempDate = Date
holdYear = Right(tempDate, 4)
holdYear = holdYear - 2000
holdLen = Len(tempDate)
slashPosition = InStr(1, tempDate, "/", 1)
holdMonth = Left(tempDate, slashPosition - 1)
monthAndYearOnly = Right(tempDate, holdLen - slashPosition)
slashPosition = InStr(1, monthAndYearOnly, "/", 1)
holdDay = Left(monthAndYearOnly, slashPosition - 1)
afterLeapyear = 5


daysLeftInStartofYearWeek = 7
If holdYear = 0 Then
daysLeftInStartofYearWeek = 1
Else

For i = 1 To holdYear
If daysLeftInStartofYearWeek = 1 Then
daysLeftInStartofYearWeek = 8
End If
If i = afterLeapyear Then
afterLeapyear = afterLeapyear + 4
daysLeftInStartofYearWeek = daysLeftInStartofYearWeek - 2
Else
daysLeftInStartofYearWeek = daysLeftInStartofYearWeek - 1
End If

Next i

End If

If holdYear Mod 4 = 0 Then
February = 29
days = 366
Else
February = 28
days = 365
End If

January = 31
March = 31
April = 30
May = 31
June = 30
July = 31
August = 31
September = 30
October = 31
November = 30
December = 31


daysBeforeFebruary = January
daysBeforeMarch = daysBeforeFebruary + February
daysBeforeApril = daysBeforeMarch + March
daysBeforeMay = daysBeforeApril + April
daysBeforeJune = daysBeforeMay + May
daysBeforeJuly = daysBeforeJune + June
daysBeforeAugust = daysBeforeJuly + July
daysBeforeSeptember = daysBeforeAugust + August
daysBeforeOctober = daysBeforeSeptember + September
daysBeforeNovember = daysBeforeOctober + October
daysBeforeDecember = daysBeforeNovember + November

Select Case holdMonth
Case 1
daysUpToCurrent = holdDay
Case 2
daysUpToCurrent = daysBeforeFebruary + holdDay
Case 3
daysUpToCurrent = daysBeforeMarch + holdDay
Case 4
daysUpToCurrent = holdDay + daysBeforeApril
Case 5
daysUpToCurrent = holdDay + daysBeforeMay
Case 6
daysUpToCurrent = holdDay + daysBeforeJune
Case 7
daysUpToCurrent = holdDay + daysBeforeJuly
Case 8
daysUpToCurrent = holdDay + daysBeforeAugust
Case 9
daysUpToCurrent = holdDay + daysBeforeSeptember
Case 10
daysUpToCurrent = holdDay + daysBeforeOctober
Case 11
daysUpToCurrent = holdDay + daysBeforeNovember
Case 12
daysUpToCurrent = holdDay + daysBeforeDecember
End Select

daysUpToCurrent = CInt(daysUpToCurrent)
If daysUpToCurrent <= daysLeftInStartofYearWeek Then
GetWeeksBeginningSunday = 1
Else
decweek = ((daysUpToCurrent - (daysLeftInStartofYearWeek + 1)) / 7) +
2
intweek = CInt(decweek)
If intweek decweek Then
intweek = intweek - 1
End If
GetWeeksBeginningSunday = intweek
End If
End Function


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements