Day Of Week Functions

This page describes a number of formulas and VBA functions you can use when working with days of the week.

Introduction

Many, if not most, workbooks work with dates in one fashion or another. This page describes 9 functions, implemented both as worksheet formulas and as VBA functions, that you can use when working with days of the week.

In all functions, the day of the week is expressed as a number, where 1 = Sunday, 2 = Monday, ..., 7 = Saturday. It should also be noted that most of the functions use a modulus operation, Excel's MOD worksheet function. The MOD worksheet function and the VBA Mod operator can produce different results, specifically when negative numbers are involved. In order to maintain logical continuity between the worksheet formulas presented here and their VBA function equivalents, the VBA code uses a function named WSMod that behaves the same as the MOD worksheet function. If you write your own VBA functions based on the code provided here, you will want to use the WSMod function rather than VBA's Mod operator.

You can download the XLS workbook with all the formulas and VBA code, or you can download the BAS module file containing the VBA code.

Day Of Week Functions

The following formulas and VBA functions are described on this page and are available in the download files.

• DaysOfWeekBetweenTwoDates
  This returns the number of Day Of Week days between two dates. For example, the number of Tuesdays between 15-Jan-2009 and 26-July-2010.
  
  
• DaysOfWeekInMonth This returns the number of a given Day Of Week in a given month and year. For example, the number of Tuesdays in April, 2009.
• DateOfPreviousDayOfWeek
  This returns the date of the first Day Of Week before a given date. For example, the date of the Tuesday before 15-June-2009.
  
  
• DateOfNextDayOfWeek
  This returns the date of the first Day Of Week following a given date. For example, the date of the first Tuesday after 15-June-2009.
  
  
• FirstDayOfWeekInMonth
  This returns the date of the first Day Of Week day in a given month and year. For example, the date of the first Friday in March, 2010.
  
  
• LastDayOfWeekInMonth
  This returns the date of the last Day Of Week day in a given month and year. For exampe, the date of the last Friday in May, 2009.
  
  
• NthDayOfWeekInMonth
  This returns the date of the Nth Day Of Week day in a given month and year. For example, the date of the third Friday in May, 2009.
  
  
• FirstDayOfWeekOfYear
  This returns the date of the first Day Of Week day of a given year. For example, the date of the first Friday in 2009.
  
  
• LastDayOfWeekOfYear
  This returns the date of the last Day Of Week day of a given year. For example, the date of the last Monday in 2009.
  
  

These functions, in both worksheet formula and VBA implementations, are described below. The WSMod function, which is used in place of VBA's Mod operator, is as follows:

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

Days Of Week Between Two Dates

This returns the number of Day Of Week days between two dates. For example, the number of Tuesdays between 6-Jan-2009 and 31-Jan-2009 is 4.

=((EndDate-MOD(WEEKDAY(EndDate)-DayOfWeek,7)-
StartDate-MOD(DayOfWeek-WEEKDAY(StartDate)+7,7))/7)+1

In VBA,

Public Function DaysOfWeekBetweenTwoDates(StartDate As Date, _
    EndDate As Date, DayOfWeek As VbDayOfWeek) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DaysOfWeekBetweenTwoDate
' This function returns the number of DaysOfWeek between StartDate and
' EndDate. StartDate is the first date, EndDate is the last date, and
' DayOfWeek is an long  between 1 and 7 (1 = Sunday, 2 = Monday, ...
' 7 = Saturday). If StartDate is later than EndDate, the result is #NUM!.
' If DayOfWeek is out of range, the result is #VALUE.
' Note that this function uses WSMod to use Excel's worksheet function MOD
' rather than VBA's Mod operator.
'
' Worksheet function equivalent:
'
' =((EndDate-MOD(WEEKDAY(EndDate)-DayOfWeek,7)-StartDate-
'   MOD(DayOfWeek-WEEKDAY(StartDate)+7,7))/7)+1
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If StartDate > EndDate Then
    DaysOfWeekBetweenTwoDates = CVErr(xlErrNum)
    Exit Function
End If
If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    DaysOfWeekBetweenTwoDates = CVErr(xlErrValue)
    Exit Function
End If
If (StartDate < 0) Or (EndDate < 0) Then
    DaysOfWeekBetweenTwoDates = CVErr(xlErrValue)
    Exit Function
End If

DaysOfWeekBetweenTwoDates = _
    ((EndDate - WSMod(Weekday(EndDate) - DayOfWeek, 7) - StartDate - _
    WSMod(DayOfWeek - Weekday(StartDate) + 7, 7)) / 7) + 1

End Function

Days Of Week In Month

This returns the number of Day Of Week days in a given month and year. For example, the number of Sundays in January, 2009, is 5.

=((DATE(YYear,MMonth+1,0)-MOD(WEEKDAY(DATE(YYear,MMonth+1,0))-DayOfWeek,7)-
DATE(YYear,MMonth,1)-MOD(DayOfWeek-WEEKDAY(DATE(YYear,MMonth,1))+7,7))/7)+1

In VBA,

Public Function DaysOfWeekInMonth(MMonth As Long, YYear As Long, _
    DayOfWeek As VbDayOfWeek) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' DaysOfWeekInMonth
' This function returns the number of DaysOfWeek in the month MMonth in
' year YYear.  If either the MMonth or YYear value is out of range, the
' result is #VALUE.
' Note that this function uses WSMod to use Excel's worksheet function MOD
' rather than VBA's Mod operator.
'
' Formula equivalent:
'   =((DATE(YYear,MMonth+1,0)-MOD(WEEKDAY(DATE(YYear,MMonth+1,0))-DayOfWeek,7)-
'       DATE(YYear,MMonth,1)-MOD(DayOfWeek-WEEKDAY(DATE(YYear,MMonth,1))+7,7))/7)+1
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If (MMonth < 1) Or (MMonth > 12) Then
    DaysOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If
If (YYear < 1900) Or (YYear > 9999) Then
    DaysOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If

If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    DaysOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If

DaysOfWeekInMonth = ((DateSerial(YYear, MMonth + 1, 0) - _
    WSMod(Weekday(DateSerial(YYear, MMonth + 1, 0)) - DayOfWeek, 7) - _
    DateSerial(YYear, MMonth, 1) - WSMod(DayOfWeek - _
    Weekday(DateSerial(YYear, MMonth, 1)) + 7, 7)) / 7) + 1

End Function

Date Of Previous Day Of Week

This function returns the date of the first Day Of Week day prior to a given date. For example, the Tuesday prior to 31-Jan-2009 is 27-Jan-2009.

=StartDate-MOD(WEEKDAY(StartDate)-DayOfWeek,7)

In VBA,

Public Function PreviousDayOfWeek(StartDate As Date, _
    DayOfWeek As VbDayOfWeek) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' PreviousDayOfWeek
' This function returns the date of the DayOfWeek prior to StartDate.
' Note that this function uses WSMod to use Excel's worksheet function MOD
' rather than VBA's Mod operator.
' Formula equivalent:
'  =StartDate-MOD(WEEKDAY(StartDate)-DayOfWeek,7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    PreviousDayOfWeek = CVErr(xlErrValue)
    Exit Function
End If
If (StartDate < 0) Then
    PreviousDayOfWeek = CVErr(xlErrValue)
    Exit Function
End If

PreviousDayOfWeek = StartDate - WSMod(Weekday(StartDate) - DayOfWeek, 7)

End Function

Next Day Of Week

This returns the date of the first Day Of Week day following a given date. The Sunday following 15-Jan-2009 is 18-Jan-2009.

=StartDate+MOD(DayOfWeek-WEEKDAY(StartDate),7)

In VBA,

Public Function NextDayOfWeek(StartDate As Date, _
    DayOfWeek As VbDayOfWeek) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NextDayOfWeek
' This function returns the date of the DayOfWeek following StartDate.
' Note that this function uses WSMod to use Excel's worksheet function MOD
' rather than VBA's Mod operator.
' Formula equivalent:
' =StartDate+MOD(DayOfWeek-WEEKDAY(StartDate),7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    NextDayOfWeek = CVErr(xlErrValue)
    Exit Function
End If
If (StartDate < 0) Then
    NextDayOfWeek = CVErr(xlErrValue)
    Exit Function
End If

NextDayOfWeek = StartDate + WSMod(DayOfWeek - Weekday(StartDate), 7)

End Function

First Day Of Week In Month

This returns then first Day Of Week in a given month and year. For example, the first Sunday in June, 2009, is 7-June-2009.

=DATE(YYear,MMonth,1)+(MOD(DayOfWeek-WEEKDAY(DATE(YYear,MMonth,1)),7))

In VBA,

Public Function FirstDayOfWeekInMonth(MMonth As Long, YYear As Long, _
    DayOfWeek As VbDayOfWeek) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' This returns the date of the first DayOfWeek in month MM in year YYYY.
' Formula equivalent:
'   =DATE(YYear,MMonth,1)+(MOD(DayOfWeek-WEEKDAY(DATE(YYear,MMonth,1)),7))
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    FirstDayOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If
    
If (MMonth < 1) Or (MMonth > 12) Then
    FirstDayOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If
    
FirstDayOfWeekInMonth = DateSerial(YYear, MMonth, 1) + _
    WSMod(DayOfWeek - Weekday(DateSerial(YYear, MMonth, 1)), 7)

End Function

Last Day Of Week In Month

This returns the last Day Of Week day in a given month and year. For example, the last Wednesday in November, 2009, is 28-November-2009.

=DATE(YYear,MMonth+1,0)-ABS(WEEKDAY(DATE(YYear,MMonth+1,0))-DayOfWeek)

In VBA,

Public Function LastDayOfWeekInMonth(MMonth As Long, YYear As Long, _
    DayOfWeek As VbDayOfWeek) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' LastDayOfWeekInMonth
' This returns the date of the last DayOfWeek in month MM in year YYYY.
' Formula equivalent:
'       =DATE(YYear,MMonth+1,0)-ABS(WEEKDAY(DATE(YYear,MMonth+1,0))-DayOfWeek)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    LastDayOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If

LastDayOfWeekInMonth = DateSerial(YYear, MMonth + 1, 0) - _
    Abs(Weekday(DateSerial(YYear, MMonth + 1, 0)) - DayOfWeek)

End Function

Nth Day Of Week In Month

This returns the Nth Day Of Week day in a given month and year. For example, the third Thursday of September, 2009, is 17-Sept-2009.

=DATE(YYear,MMonth,1)+(MOD(DayOfWeek-WEEKDAY(DATE(YYear,MMonth,1)),7))+(7*(Nth-1))

In VBA,

Public Function NthDayOfWeekInMonth(MMonth As Long, YYear As Long, _
    DayOfWeek As VbDayOfWeek, Nth As Long) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' NthDayOfWeekInMonth
' This returns the Nth Day Of Week in month MM in year YYYY.
' Formula equivalent:
'
'   =DATE(YYear,MMonth,1)+(MOD(DayOfWeek-WEEKDAY(DATE(YYear,MMonth,1)),7))+(7*(Nth-1))
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
If (MMonth < 1) Or (MMonth > 12) Then
    NthDayOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If
If (YYear < 1900) Or (YYear > 9999) Then
    NthDayOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If

If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    NthDayOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If

If Nth < 0 Then
    NthDayOfWeekInMonth = CVErr(xlErrValue)
    Exit Function
End If

NthDayOfWeekInMonth = DateSerial(YYear, MMonth, 1) + _
    (WSMod(DayOfWeek - Weekday(DateSerial(YYear, MMonth, 1)), 7)) + _
    (7 * (Nth - 1))

End Function

First Day Of Week In Year

This returns the date of the first Day Of Week day in a given year. For example, the first Tuesday in 2009 is 6-Jan-2009.

=DATE(YYear,1,1)+MOD(DayOfWeek-WEEKDAY(DATE(YYear,1,1)),7)

In VBA,

Public Function FirstDayOfWeekOfYear(YYear As Long, DayOfWeek As VbDayOfWeek) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FirstDayOfWeekOfYear
' This returns the date of the first DayOfWeek in the year YYear.
' Formula equivalent:
'   =DATE(YYear,1,1)+MOD(DayOfWeek-WEEKDAY(DATE(YYear,1,1)),7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If (YYear < 1900) Or (YYear > 9999) Then
    FirstDayOfWeekOfYear = CVErr(xlErrValue)
    Exit Function
End If

If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    FirstDayOfWeekOfYear = CVErr(xlErrValue)
    Exit Function
End If
FirstDayOfWeekOfYear = DateSerial(YYear, 1, 1) + _
    WSMod(DayOfWeek - Weekday(DateSerial(YYear, 1, 1)), 7)

End Function

Last Day Of Week Of Year

This returns the date of the last Day Of Week day in a given year. For example, the last Wednesday in 2009 is 30-Dec-2009.

=DATE(YYear,12,31)-MOD(WEEKDAY(DATE(YYear,12,31))-DayOfWeek,7)

In VBA,

Public Function LastDayOfWeekOfYear(YYear As Long, DayOfWeek As VbDayOfWeek) As Variant
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' LastDayOfWeekOfYear
' This returns the last DayOfWeek of the year YYear.
' Formula equivalent:
'   =DATE(YYear,12,31)-MOD(WEEKDAY(DATE(YYear,12,31))-DayOfWeek,7)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

If (YYear < 1900) Or (YYear > 9999) Then
    LastDayOfWeekOfYear = CVErr(xlErrValue)
    Exit Function
End If

If (DayOfWeek < vbSunday) Or (DayOfWeek > vbSaturday) Then
    LastDayOfWeekOfYear = CVErr(xlErrValue)
    Exit Function
End If

LastDayOfWeekOfYear = DateSerial(YYear, 12, 31) - _
    WSMod(Weekday(DateSerial(YYear, 12, 31)) - DayOfWeek, 7)

End Function

You can download the XLS workbook with all the formulas and VBA codem, or you can download the BAS module file containing the VBA code.

This page last updated: 15-August-2009.