This Page: www.cpearson.com/Excel/DayOfWeekFunctions.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 27-Oct-2014

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

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. |