|
» Weeknumbers using VBA in Microsoft Excel
VBA macro tip contributed by
Erlandsen
Data Consulting offering Microsoft Excel Application development,
template customization, support and training solutions
|
|
 | |
CATEGORY: Custom Functions , Printing in VBA |
VERSIONS: All Microsoft Excel Versions |
|
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
|
|
RATING:
4.02
VIEWS: 31128 |
||||
Sponsors:
 |
Book Store:
Recommended Books:
- Retire Young, Retire Rich
- Keys to Reading an Annual Report (Barron's Business Keys)
- The Laws of Money, The Lessons of Life: Keep What You Have and Create What You Deserve
- The Guide to Understanding Financial Statements
- Microsoft Office XP Introductory Concepts and Techniques
- Fish! A Remarkable Way to Boost Morale and Improve Results
Related MS EXCEL TIPS:
Terms
and Conditions of use
The applications/code on this site are distributed as is and without warranties
or liability. In no event shall the owner of the copyrights, or the authors
of the applications/code be liable for any loss of profit, any problems
or any damage resulting from the use or evaluation of the applications/code.
