Week Numbers In Excel

This page describes how to work with week numbers in Excel.

Introduction

Many applications and organizations use the week number of a date for some sort of identification or formatting purpose. However, what constitutes a week number, specifically when does Week 1 begin, is not always the same from one organization or application to another. This page describes worksheet formulas and VBA procedures that you can use to calculate a week number in a number of different manners.

You can download a workbook with all the formulas and functions on this page or you can download a VBA bas file containing the VBA functions.

The formulas and functions described on this page work with the following definitions of the first day of week 1 of a year:

• The first day of Week 1 is January of that year, regardless of what day of the week that might be.
  
• The first day of Week 1 is the first occurrence of some specified day of the week. For example, you may define Week 1 to begin on the first Monday of the year.
  
• The first day of Week 1 is a specific date. For example, you may define that Week 1 always begins on May 1 of the year.
  
• The first day of Week 1 is the first occurrence of one day of the week following the first occurrence of another day of the week. For example, you may define that Week 1 begins on the first Monday after the first Thursday of the year.
  
• The first day of Week 1 is specified by the Internation Standards Organization (ISO) definiton.

Absolute Week Number

This is the simplest method. It assumes that Week 1 begins on January 1, regardless of what day of the week that might be. The formula for this is:

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7)

where StartDate is the date whose week number is to be returned.

The VBA code for this function is:

Public Function WeekNumberAbsolute(DT As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberAbsolute
' This returns the week number of the date in DT based on Week 1 starting
' on January 1 of the year of DT, regardless of what day of week that
' might be.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,0))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WeekNumberAbsolute = Int(((DT - DateSerial(Year(DT), 1, 0)) + 6) / 7)
End Function

Week Number First Day Of Week

This method uses as the first day of Week 1 the first occurrence of a specified day of the week. For example, you might define that Week 1 begins on the first Monday of the year. The formula is:

=TRUNC(((StartDate-DATE(YEAR(StartDate),1,1)+
MOD(DayOfWeek-WEEKDAY(DATE(YEAR(StartDate),1,1)),7))+6)/7)

where StartDate is the whose week number is to be found, and DayOfWeek is the day of week number (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

The VBA code for this function is:

Public Function WeekNumberFromFirstDayOfWeek(DT As Date, DayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromFirstDayOfWeek
' This returns the week number of the date DT based on Week 1 starting
' on the first DayOfWeek of the year of DT.
' Formula equivalent:
'       =TRUNC(((DT-DATE(YEAR(DT),1,1)+MOD(DayOfWeek-WEEKDAY(DATE(YEAR(DT),1,1)),7))+6)/7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
WeekNumberFromFirstDayOfWeek = Int(((DT - DateSerial(Year(DT), 1, 1) + _
    WSMod(DayOfWeek - Weekday(DateSerial(Year(DT), 1, 1)), 7)) + 6) / 7)
End Function

Week Number From Date

This method uses a specific date as the first day of Week 1. The formula is:

=TRUNC(((TestDate-StartDate)+6)/7)+(WEEKDAY(TestDate)=WEEKDAY(StartDate))

where TestDate is the date whose week number is to be calcuatled, and StartDate specifies the first day of Week 1.

The VBA code for this function is:

Public Function WeekNumberFromDate(DT As Date, StartDate As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromDate
' This returns the week number base on Week 1 starting on StartDate.
'       =TRUNC(((DT-StartDate)+6)/7)+(WEEKDAY(DT)=WEEKDAY(StartDate))
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   WeekNumberFromDate = Int(((DT - StartDate) + 6) / 7) + Abs(Weekday(DT) = Weekday(StartDate))
End Function

Week Number From First Day Of Week Following Another Day Of Week

This methods uses as the first day of Week 1 the first occurrence of one specified day of the week that follows the first occurrence of another day of the week. For example, you might want the first day of week to be the Monday following the first Thursday of the year. The formula is:

=TRUNC(((TestDate-((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))+
(MOD(StartDayOfWeek-WEEKDAY((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))),7))))+6)/7)+
(WEEKDAY(TestDate)=WEEKDAY(((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))+
(MOD(StartDayOfWeek-WEEKDAY((DATE(YEAR(TestDate),1,1)+
MOD(BaseDayOfWeek-WEEKDAY(DATE(YEAR(TestDate),1,1)),7))),7)))))

where TestDate is the date whose week number is to be calculated. StartDayOfWeek is the number of the day of the week after which BaseDate is to be found. BaseDayOfWeek is the weekday number of the day following the first occurrence of BaseDayOfWeek that week 1 should begin. For example, to use the first Monday following the first Thursday of the year, BaseDayOfWeek should be 2 and StartDate should be 5.

The VBA code for this function is:

Public Function WeekNumberFromDayOfWeekDay(DT As Date, BaseDayOfWeek As VbDayOfWeek, _
    StartDayOfWeek As VbDayOfWeek) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberFromDayOfWeekDay
' This returns the week number based on Week 1 starting on the first
' StartDayOfWeek following the first BaseDayOfWeek day of week in the
' year of DT. E.g., the week number of 8-March-2009 based on Week 1
' beginning on the first StartDayOfWeek = Wednesday following the first
' BaseDayOfWeek = Sunday of the year 2009. That is, find the first Sunday,
' go to the next Wednesday, and begin Week 1 on that Wednesday.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FirstDayOfYear As Long
Dim FirstDayOfYearWeekday As Long

FirstDayOfYear = DateSerial(Year(DT), 1, 1)
FirstDayOfYearWeekday = Weekday(FirstDayOfYear)

WeekNumberFromDayOfWeekDay = Int(((DT - ((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7)) + _
        (WSMod(StartDayOfWeek - Weekday((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7))), 7)))) + 6) / 7) + _
        (Weekday(DT) = Weekday(((FirstDayOfYear + WSMod(BaseDayOfWeek - _
        FirstDayOfYearWeekday, 7)) + _
        (WSMod(StartDayOfWeek - Weekday((FirstDayOfYear + _
        WSMod(BaseDayOfWeek - FirstDayOfYearWeekday, 7))), 7)))))

End Function

ISO Week Number

This method uses the International Standards Organization (ISO) definition of a week number. The formula is:

=INT((StartDate-DATE(YEAR(StartDate-WEEKDAY(StartDate-1)+4),1,3)+
WEEKDAY(DATE(YEAR(StartDate-WEEKDAY(StartDate-1)+4),1,3))+5)/7)

where StartDate is the date whose week number is to be calculated.

The VBA code for this function is:

Public Function WeekNumberISO(InDate As Date) As Long
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WeekNumberISO
' This returns the ISO Standard week number.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim D As Date
    D = DateSerial(Year(InDate - Weekday(InDate - 1) + 4), 1, 3)
    WeekNumberISO = Int((InDate - D + Weekday(D) + 5) / 7)
End Function

The WSMod Function

The VBA Mod operator and the MOD worksheet function do not always return the same result, particularly when negative numbers are involved. To maintain the continuity between the VBA functions and their worksheet formula counterparts, the VBA functions us a function named WSMod that works as does the Excel MOD function. WSMod is required by many of the functions on this page and you should use it, rather than VBA's Mod operator if you modify these functions.

The VBA code for this function is:

Private Function WSMod(Number As Double, Divisor As Double) As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' WSMod
' The Excel worksheet function MOD and the VBA Mod operator
' work differently and can return different results under
' certain circumstances. For continuity between the worksheet
' formulas and the VBA code, we use this WSMod function, which
' produces the same result as the Excel MOD worksheet function,
' rather than the VBA Mod operator.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    WSMod = Number - Divisor * Int(Number / Divisor)
End Function

You can download a workbook with all the formulas and functions on this page or you can download a VBA bas file containing the VBA functions.

This page last updated: 3-September-2009.

Created by Chip Pearson at Pearson Software Consulting, LLC
Email: chip@cpearson.com Before emailing me, please read this page. 
http://cpearson.com/excel/WeekNumbers.aspx
Copyright © 1997 - 2009, Charles H. Pearson

Submit bug information or errors on the Bug And Error Report Page.



About This Web Site Activate The Main Excel Window Adding Dates Adding The COM Add-Ins Menu Item Adding the COM Add-Ins menu item Alerter Display Library Alerter Message Display Component Analysis ToolPak Application Events Array Formulas Array Formulas Arrays In VBA Arrays To Columns Attachments In Newsgroups Automation Add-Ins Blinking Text Blinking Text Bracket Pricing Browse Folder CALL Function Call Stack Calling Worksheet Functions Cell Referencing Cell View Add-In Class Modules Clipboard Functions Clone A Folder Code Modules Code Notes Collections And Dictionaries Color Band Formatting Colors And Functions COM Add-Ins In VB6 Conditional Formatting Conditional Formatting Colors Connected And Disconnected Objects Consulting And Development Services Creating An Add-In Date And Time Quick Entry Date And Time VBA Functions Date And Time Worksheet Functions Date And Times In Excel Date Arithmetic Date Intervals DATEDIF Function Dates And Times Daylight Savings Time Debugging VBA Declaring Variables Default Property Of A Class Deleting Duplicates Deleting Duplicates With Filter Detecting Scrolling Direct Connect The MSNEWS Directory Tree Listings Distributed Dates Document Properties Downloadable Files Downloadable Zip Files Downloads Duplicate Data In Lists And Ranges Duplicates In Lists Email Me Emailing ME Empty A Folder Enum Data Types Error Handling In VBA Error Values And The FormatMessage API EULA Answers Event Procedures Events In UserForms Excel Macros Excel Pages Excel Shutdown Excel Tools Excel Worksheet Functions Feet And Inches File Name Of COM Add-In File Times Find In Range First And Last Names Folder Tree Listing Folder Tree Listing Form Positioner Formula Bar Shortcut Fractional Arithmetic Functions And Subs Games Get And Set Properties GetInfo Function Headers And Footers Hidden Namespace Holidays Import And Export Text Importing Large Files To Excel Importing Large Text Files INDIRECT Function Insert And Fill A Series Insert Procedure Names In VBA Intro To VBA Julian Dates Keyboard Shortcuts Keyboard Tests Last Time Of File Latitude And Longitude Legalese Legalese and Copyright Information Links List Lookups ListBox Utlities Lists Of Data In Excel Lists On Worksheets Local And GMT Times Main Page Main Page Make Mulitple Levels Of Directories Menu Items Menus In The VBA Editor Modifying The Registry Multi File Import In VBA Multiple Monitors And Windows Name Box Shortcut Named Ranges Nested Functions New Posters Guide For Newsgroups No Blanks No Dupicate Entries Office Application Window Classes Optimize Your VBA Code Ordinal Number Suffixes Overtime And Timesheets Parsing Phone Numbers Passing And Returning Arrays In VBA Passwords Pivot Tables Popup Box Privacy Statement Progress Bar Random Numbers Ranking Data Recursive Programming Recycle A File Or Folder References Registry Related Pages Relative And Absolute References Relative And Absolute References Resources For Developers Returning Arrays Rounding In Excel Rowliner Add-In Save Copy And Zip A Workbook Save Coy And Zip Add-In Scheduling Procedures With OnTime Search The Site Search The Site Series Information SetParent And Userforms Sheet Referencing Shell And Wait Shortcut Keys Shortcut Keys For VBA Show Any Form Site Feedback Size String Some Friendly Sort And Array In VBA Sort By Color Sort Worksheet Tabs Sorting Arrays Of Objects Sorting Objects Comparison Special Characters Special User Folders Startup Errors In Excel Status Bar In Excel Subclassing Windows Support Suppress Changes In Forms System Error Messages From Numbers Temporary Files And Folders Test Friendly Text Box Validation Time Zones Timed Close Topic Index Truly Global Variables Unselect A Cell Or Area Upper And Lower Case User Defined Functions User Defined Functions Validate A Text Box Values And Formulas Variables In VBA VBA Code To Modify VBA Code Versions Of Excel Wait For File Close Week Numbers What's New On The Site Widen The Name Box Word Count Of Worksheet Zomming And Centering  
0 - Rate This Page 1 - Not Helpful 2 - Somewhat Helpful 3 - Quite Helpful 4 - Very Helpful 5 - Exactly What I Needed


Essential Tools For Developers

  

Essential Tools For Financial Analysts And Accounting Professionals

  
Ready


Advertise Your Product On This Site