|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» Weeknumbers using VBA in Microsoft Excel
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
The function WEEKNUM() in the Analysis Toolpack addin calculates the correct week number for a given date, if you are in the U.S. The user defined function below will calculate the correct week number depending on the national language settings on your computer.
Function UDFWeekNum(InputDate As Date)
UDFWeekNum = DatePart("ww", InputDate, vbUseSystemDayOfWeek, vbUseSystem)
End Function
The function above can also be modified to calculate the weeknumber the European way:
Function UDFWeekNumISO(InputDate As Date)
UDFWeekNumISO = DatePart("ww", InputDate, vbMonday, vbFirstFourDays)
End Function
The two functions above can, due to a bug, return a wrong week number. This occurs for dates around New Year for some years, e.g. the years 1907, 1919, 1991, 2003, 2007, 2019 and 2091. You can use this worksheet formula to calculate the correct week number (Thanks to George Simms, GeorgeSim@email.msn.com, for pointing this out): =INT((A1-(DATE(YEAR(A1+(MOD(8-WEEKDAY(A1),7)-3)),1,1))-3+ MOD(WEEKDAY(DATE(YEAR(A1+(MOD(8-WEEKDAY(A1),7)-3)),1,1))+1,7))/7)+1 The formula above assumes that cell A1 contains a valid date for which you want to return the week number. To calculate the correct week number with a user-defined VBA function, you can use the function below:
Function WEEKNR(InputDate As Long) As Integer
Dim A As Integer, B As Integer, C As Long, D As Integer
WEEKNR = 0
If InputDate < 1 Then Exit Function
A = Weekday(InputDate, vbSunday)
B = Year(InputDate + ((8 - A) Mod 7) - 3)
C = DateSerial(B, 1, 1)
D = (Weekday(C, vbSunday) + 1) Mod 7
WEEKNR = Int((InputDate - C - 3 + D) / 7) + 1
End Function
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
Good to Great: Why Some Companies Make the Leap... and Others Don't
Managerial Accounting: Tools for Business Decision Making, WebCT, 2nd Edition
Microsoft Office Xp: Advanced Concepts and Techniques: Word 2002, Excel 2002, Access 2002, Powerpoint 2002
Rich Dad's Guide to Investing: What the Rich Invest in, That the Poor and the Middle Class Do Not!
Marketing Plans
Financial Reporting and Analysis (2nd Edition) | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|