Is there a diffenence in the result?
WEEKNUM
See Also
Returns a number that indicates where the week falls numerically within a
year.
If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.
How?
1.. On the Tools menu, click Add-Ins.
2.. In the Add-Ins available list, select the Analysis ToolPak box, and
then click OK.
3.. If necessary, follow the instructions in the setup program.
Syntax
WEEKNUM(serial_num,return_type)
Serial_num is a date within the week. Dates should be entered by using the
DATE function, or as results of other formulas or functions. For example,
use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if
dates are entered as text.
Return_type is a number that determines on which day the week begins. The
default is 1.
Serial_num Week Begins
1 Week begins on Sunday. Weekdays are numbered 1 through 7.
2 Week begins on Monday. Weekdays are numbered 1 through 7.
Remark
Microsoft Excel stores dates as sequential serial numbers so they can be
used in calculations. By default, January 1, 1900 is serial number 1, and
January 1, 2008 is serial number 39448 because it is 39,448 days after
January 1, 1900. Microsoft Excel for the Macintosh uses a different date
system as its default.
Example
The example may be easier to understand if you copy it to a blank worksheet.
How?
1.. Create a blank workbook or worksheet.
2.. Select the example in the Help topic. Do not select the row or column
headers.
Selecting an example from Help
3.. Press CTRL+C.
4.. In the worksheet, select cell A1, and press CTRL+V.
5.. To switch between viewing the results and viewing the formulas that
return the results, press CTRL+` (grave accent), or on the Tools menu, point
to Formula Auditing, and then click Formula Auditing Mode.
1
2
A
Data
March 9, 2008
Formula Description (Result)
=WEEKNUM(A2,1) Number of the week in the year, with a week
beginning on Sunday (11)
=WEEKNUM(A2,2) Number of the week in the year, with a week
beginning on Monday (10)
Note March 9, 2008 is a Sunday.
--
Don Guillett
SalesAid Software
"reesmacleod" wrote in message
...
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