Excel WEEKDAY Function

The Excel WEEKDAY function returns a number between 1 and 7, representing the day of the week based on the return type that has been selected

Example: Excel WEEKDAY Function

Excel WEEKDAY Function

METHOD 1. Excel WEEKDAY Function using hardcoded values

EXCEL

=WEEKDAY(B5,1)
Result in cell D5 (4) - returns a number that represents the day of the week for 15/03/2017 based on the Number 1 (Sunday) through 7 (Saturday) type.

=WEEKDAY(B6,2)
Result in cell D6 (3) - returns a number that represents the day of the week for 15/03/2017 based on the Number 1 (Monday) through 7 (Sunday) type.

METHOD 2. Excel WEEKDAY Function using links

EXCEL

=WEEKDAY(B5,C5)
Result in cell D5 (4) - returns a number that represents the day of the week for 15/03/2017 based on the Number 1 (Sunday) through 7 (Saturday) type.

=WEEKDAY(B6,C6)
Result in cell D6 (3) - returns a number that represents the day of the week for 15/03/2017 based on the Number 1 (Monday) through 7 (Sunday) type.

METHOD 3. Excel SUM function using the Excel built-in function library with hardcoded values

EXCEL

Formulas tab > Function Library group > Date & Time > WEEKDAY > populate the input boxes

=WEEKDAY(B5,1)
Note: in this example we are representing 15/03/2017 as the day of the week number, based on the Number 1 (Sunday) through 7 (Saturday) type. 		Built-in Excel WEEKDAY Function using hardocded values

METHOD 4. Excel WEEKDAY function using the Excel built-in function library with links

EXCEL

Formulas tab > Function Library group > Date & Time > WEEKDAY > populate the input boxes

=WEEKDAY(B5,C5)
Note: in this example we are representing 15/03/2017 as the day of the week number, based on the Number 1 (Sunday) through 7 (Saturday) type. 		Built-in Excel WEEKDAY Function using links

METHOD 1. Excel WEEKDAY function using VBA

VBA

Sub Excel_WEEKDAY_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("WEEKDAY")

'apply the Excel WEEKDAY function
ws.Range("D5") = Weekday(ws.Range("B5"), 1)
ws.Range("D6") = Weekday(ws.Range("B6"), 2)

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named WEEKDAY.
Date Range: Ensure that the date for which you want to find the day of the week is captured in range ("B5:B6") in the worksheet.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") and ("D6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Date Range: Select the range were the dates are stored by changing the range ("B5:B6") in the VBA code to any range in the worksheet that contains the dates and doesn't conflict with the formula.

METHOD 2. Excel WEEKDAY function using VBA with links

VBA

Sub Excel_WEEKDAY_Function()
'declare a variable
Dim ws As Worksheet
Set ws = Worksheets("WEEKDAY")

'apply the Excel WEEKDAY function
ws.Range("D5") = Weekday(ws.Range("B5"), ws.Range("C5"))
ws.Range("D6") = Weekday(ws.Range("B6"), ws.Range("C6"))

End Sub

OBJECTS
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
PREREQUISITES
Worksheet Name: Have a worksheet named WEEKDAY.
Date Range: Ensure that the date for which you want to find the day of the week is captured in range ("B5:B6") in the WEEKDAY worksheet.
Type: Ensure that the return type is captured in range ("C5:C6") in the WEEKDAY worksheet.

ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("D5") and ("D6") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Date Range: Select the range were the dates are stored by changing the range ("B5:B6") in the VBA code to any range in the worksheet that contains the dates and doesn't conflict with the formula.
Type: Select the return type by changing the range ("B5:B6") in the VBA code to any range in the worksheet that contains the return type and doesn't conflict with the formula.

Usage of the Excel WEEKDAY function and formula syntax

EXPLANATION

DESCRIPTION
The Excel WEEKDAY function returns a number between 1 and 7, representing the day of the week based on the return type that has been selected.
SYNTAX
=WEEKDAY(serial_number, return_type)
ARGUMENTS
serial_number: (Required) The date for which you want to find the day of the week, based on the return type.
return_type: (Optional) The number associated with the day of the week to use. Refer to the following table for a list of options for Excel.
Value Explanation
1 Numbers 1 (Sunday) through 7 (Saturday)
2 Numbers 1 (Monday) through 7 (Sunday)
3 Numbers 0 (Monday) through 6 (Sunday)
11 Numbers 1 (Monday) through 7 (Sunday)
12 Numbers 1 (Tuesday) through 7 (Monday)
13 Numbers 1 (Wednesday) through 7 (Tuesday)
14 Numbers 1 (Thursday) through 7 (Wednesday)
15 Numbers 1 (Friday) through 7 (Thursday)
16 Numbers 1 (Saturday) through 7 (Friday)
17 Numbers 1 (Sunday) through 7 (Saturday)

ADDITIONAL NOTES
Note 1: If the return_type is omitted the WEEKDAY function will use the value of 1 (Number 1 (Sunday) through 7 (Sunday)) as the return_type argument.