Friday, April 20, 2018 – Permalink –
For most purposes, weeks are numbered with Sunday considered the first day of the week. This works most of the time, but it can be a little confusing certain years.
2004 had 53 weeks. January 1 is the only day in the first week of 2005. Week 2 starts on Sunday 1/2/2005.
Chip Pearson is the Date and Time guy:
Week Numbers In Excel
"Under the International Organization for Standardization (ISO) standard 8601, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.
The first week of 2005 should start on January 3. The first and second would be part of week 53 of 2004.
If your week starts on a different day, you can use the Analysis ToolPac function:
=WEEKNUM(A1, 2) for a week that starts on Monday, =WEEKNUM(A1) if it starts on Sunday.
Also this from ExcelTip.com:
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 shown here will calculate the correct week number depending on the national language settings on your computer."
If your work week is always Saturday through Friday then
will return 1 for 1/1/2005 through 1/7/2005, 2 for January 8-14/2005, etc.
Otherwise use 1 for Sunday through 7 for Saturday.
The last number sets these parameters:
1, Start with week in which January 1 occurs (default).
2, Start with the first week that has at least four days in the new year.
3, Start with first full week of the year.
See all Topics excel
<Doug Klippert@ 3:25 AM
Comments: Post a Comment