How to use the WEEKDAY function

Author: Oscar Cronquist Article last updated on March 03, 2022

The WEEKDAY function converts a date to a weekday number from 1 to 7. You can customize the function so the week starts with any weekday.

A weekday is a day of the week except for Saturday and Sunday, however, the WEEKDAY function returns Saturdays and Sundays as well.

WEEKDAY Function Syntax
WEEKDAY Function Arguments
WEEKDAY Function Video
WEEKDAY Function example
WEEKDAY Function not working
WEEKDAY function - show weekday name
WEEKDAYS between dates
Get Excel *.xlsx file

1. WEEKDAY Function Syntax

WEEKDAY(serial_number,[return_type])

2. WEEKDAY Function Arguments

serial_number	Required. The Excel date value you want to extract the WEEKDAY number from.
return_type	Optional. Determines the type of return value, see table below.

Return_type	Number returned
1 or omitted	Numbers 1 to 7 for Sunday to Saturday.
2	Numbers 1 to 7 for Monday to Sunday.
3	Numbers 0 to 6 for Monday to Sunday.
11	Numbers 1 to 7 for Monday to Sunday.
12	Numbers 1 to 7 for Tuesday to Monday.
13	Numbers 1 to 7 for Wednesday to Tuesday.
14	Numbers 1 to 7 for Thursday to Wednesday.
15	Numbers 1 to 7 for Friday to Thursday .
16	Numbers 1 to 7 for Saturday to Friday .
17	Numbers 1 to 7 for Sunday to Saturday.

3. Video

4. WEEKDAY function example

The WEEKDAY function shown in cell D3 in the image above calculates a number based on a date. The number represents a given weekday based on the provided value in the second argument.

The formula below has no second argument, it defaults to 1 meaning the week begins with a Sunday.

Formula in cell D3:

=WEEKDAY(B3)

5. WEEKDAY function not working

Check your spelling.
Check that you are using a valid Excel date.
Check the number of arguments.

6. WEEKDAY function - show name

WEEKDAY Function show weekday name

Formula in cell C3:

=TEXT(B3, "DDDD")

Step 1 - TEXT function

The TEXT function lets you format values.

TEXT(value, format_text)

value - The string you want to format. You can use a cell reference here or use a text string.

format_text - Formatting code allowing you to change the way, for example, a date or a number is displayed to the Excel user.

Step 2 - Populate arguments

The TEXT function has two arguments.

value - B3

format_text - "dddd"

"dddd" returns the weekday. Lower and upper case letters make no difference.

Check out this article to learn more about formatting codes in the TEXT function.

Step 3 - Evaluate TEXT function

TEXT(B3, "dddd")

becomes

TEXT(43263, "dddd")

and returns "Tuesday".

WEEKDAY Function show weekday name1

Formula in cell C4:

=INDEX({"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}, WEEKDAY(B4,1))

Explaining formula

Step 1 - Calculate number representing a weekday

WEEKDAY(B4,1)

becomes

WEEKDAY(43184, 1)

and returns 1.

Step 2 - Return weekday name based on position in array

The INDEX function returns a value in a cell range or array based on a row and column number (optional).

INDEX(array, [row_num], [column_num], [area_num])

INDEX({"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}, WEEKDAY(B4,1))

becomes

INDEX({"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday";"Saturday"}, 1)

and returns "Sunday". "Sunday" is the first value in the array.

7. Count days between two given weekdays and two dates

WEEKDAY Function given weekdays between two dates

The image above shows an Excel 365 formula that counts days between two dates and also if they are between two given weekdays.

The start date is specified in cell K3, end date is in cell K4. The start weekday is 3 which represents Tuesday, the table in J11:K18 shows the numbers and corresponding weekday names. The end weekday is 5 which represents Thursday.

The calendar in cell range B4:H9 shows which days (bolded) meet the criteria. Cell K9 displays the result, there are six bolded days in the calendar.

Excel 365 dynamic array formula in cell K6:

=LET(x,SEQUENCE(K4-K3+1,,0),y,WEEKDAY(x+K3,1),COUNT(FILTER(x,(y<=K7)*(y>=K6))))

Explaining formula

COUNT(FILTER(SEQUENCE(K4-K3+1,,0),(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<=K7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>=K6)))

Step 1 - Calculate the number of days and add one

The minus and plus signs let you perform arithmetic operations in an Excel formula

K4-K3+1

44648 - 44631 +1

equals 18.

Step 2 - Create a sequence of numbers from 0 to 18

The SEQUENCE function creates a list of sequential numbers to a cell range or array. It is located in the Math and trigonometry category and is only available to Excel 365 subscribers.

SEQUENCE(rows, [columns], [start], [step])

SEQUENCE(K4-K3+1,,0)

becomes

SEQUENCE(18,,0)

and returns

{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17}.

Step 3 - Add date to sequence of numbers

The plus sign lets you perform add numbers in an Excel formula.

SEQUENCE(K4-K3+1,,0)+K3

becomes

{0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17} + 44631

and returns

{44631; 44632; 44633; 44634; 44635; 44636; 44637; 44638; 44639; 44640; 44641; 44642; 44643; 44644; 44645; 44646; 44647; 44648}.

Step 4 - Calculate weekday

WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)

becomes

WEEKDAY({44631; 44632; 44633; 44634; 44635; 44636; 44637; 44638; 44639; 44640; 44641; 44642; 44643; 44644; 44645; 44646; 44647; 44648},1)

and returns

{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}.

Step 5 - Check if number is less than seven

The less than and larger than characters let you check if a value is smaller or larger than a condition, the result is a boolean value TRUE or FALSE. The equal sign and the less than character combined compare if values are equal or smaller than a condition.

WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<=K7

becomes

{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}<=5

and returns

{FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE}.

Step 6 - Check if weekday is larger than one

WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>=K6

becomes

{6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2; 3; 4; 5; 6; 7; 1; 2}>=3

and returns

{TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE}.

Step 7 - Multiply arrays

The asterisk character lets you multiple values in an Excel formula. Multiplying boolean values always return their numerical equivalents.

TRUE -> 1

FALSE -> 0 (zero)

(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1)

becomes

{FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE}*{TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; FALSE}

and returns

{0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0}.

Step 8 - Filter numbers based on condition

The FILTER function is a new function available to Excel 365 subscribers. It lets you extract values based on a condition or criteria.

FILTER(array, include, [if_empty])

FILTER(SEQUENCE(K4-K3+1,,0),(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1))

becomes

FILTER({0; 1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17},{0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0; 1; 1; 1; 0; 0; 0; 0})

and returns

{4; 5; 6; 11; 12; 13}.

Step 9 - Count numbers in the array

The COUNT function counts all numbers in a cell range or array.

COUNT(value1, [value2], ...)

COUNT(FILTER(SEQUENCE(K4-K3+1,,0),(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)<7)*(WEEKDAY(SEQUENCE(K4-K3+1,,0)+K3,1)>1)))

becomes

COUNT({4; 5; 6; 11; 12; 13})

and returns 6. There are six numbers in the array.

Step 10 - Shorten formula

The LET function allows you to name intermediate calculation results which can shorten formulas considerably and improve performance.

LET(name1, name_value1, calculation_or_name2, [name_value2, calculation_or_name3...])

LET(x, SEQUENCE(K4-K3+1, , 0), y, WEEKDAY(x+K3, 1), COUNT(FILTER(x, (y<7)*(y>1))))

x - SEQUENCE(K4-K3+1,,0)

y - WEEKDAY(x+K3,1)

'WEEKDAY' function examples

The following 13 articles contain the WEEKDAY function.

Calculate date given weekday and week number
This article demonstrates a formula that returns a date based on a week number and a weekday like Sun to […]

Calculate machine utilization
Question: I need to calculate how many hours a machine is utilized in a company with a night and day […]

Calendar – monthly view
This article describes how to build a calendar showing all days in a chosen month with corresponding scheduled events. What's […]

Count a specific weekday in a date range
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]

Count specific WEEKDAYS between two dates
If you want to count specific weekdays like for example Mondays and Wednesdays you need a more complicated array formula. […]

Create date ranges that stay within month
This article demonstrates a formula that creates date ranges based on a given number of days and the end date […]

How to calculate a date based on specific weekday in a month
Question: How to calculate the date of the third Monday of a given month? Answer: Column B contains dates of […]

How to highlight weekends [Conditional Formatting]
The image above shows conditional formatting applied to cell range C8:C20, it highlights cells containing dates that fall on Saturdays […]

Monthly calendar template
The image above shows a calendar that is dynamic meaning you choose year and month and the calendar instantly updates […]

Pivot Table calendar
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]

Populate cells dynamically in a weekly schedule
In this post I am going to add one more function to the weekly schedule I built in a previous […]

Setting up your work hours in a weekly schedule
The image above demonstrates conditional formatting highlighting hours outside work hours, those cells are filled with grey except weekends. Conditional formatting […]

Time sheet for work
I have built a sheet to track time at work. It is very simple, there are 13 sheets, one for […]

Functions in this article

WEEKDAY

Functions in 'Date and Time' category

The WEEKDAY function function is one of many functions in the 'Date and Time' category.

DATE function

Returns a number that acts as a date in the Excel environment.

DATEDIF function

Returns the number of days, months, or years between two dates. The DATEDIF function exists in order to ensure compatibility with Louts 1-2-3.

DATEVALUE function

Returns an Excel date value (serial number) based on a date stored as text.

DAY function

Extracts the day as a number from an Excel date.

DAYS function

Calculates the number of days between two dates.

EDATE function

Returns a date determined by a start date and a number representing how many months.

EOMONTH function

Returns an Excel date for the last day of a given month using a number and a start date.

HOUR function

Returns an integer representing the hour of an Excel time value.

MINUTE function

Returns a whole number representing the minute based on an Excel time value. The returned number is ranging from 0 to 59.

MONTH function

Extracts the month as a number from an Excel date.

NETWORKDAYS function

Returns the number of working days between two dates, excluding weekends. It also allows you to ignore a list of holiday dates that you can specify.

NETWORKDAYS.INTL function

Calculates the number of working days between two dates, excluding weekends.

NOW function

Returns the current date and time.

SECOND function

Returns an integer representing the second based on an Excel time value

TIME function

Returns a decimal value between 0 (zero) representing 12:00:00 AM and 0.99988426 representing 11:59:59 P.M.

TIMEVALUE function

Returns a decimal number based on a text string.

TODAY function

Returns the Excel date (serial number) of the current date.

WEEKDAY function

Converts a date to a weekday number from 1 to 7.

WEEKNUM function

Calculates a given date's week number based on a return_type parameter that determines which day the week begins.

WORKDAY function

Returns a date based on a start date and a given number of working days (nonweekend and nonholidays).

YEAR function

Converts a date to a number representing the year in the date.

YEARFRAC function

Returns the fraction of the year based on the number of whole days between a start date and an end date.

Excel function categories

Array manipulation

Excel functions that let you resize, combine, and shape arrays.

Compatibility

Functions for backward compatibility with earlier Excel versions. Compatibility functions are replaced with newer functions with improved accuracy. Use the new functions if compatibility isn't required.

Database

Perform basic operations to a database-like structure.

Date and Time

Functions that let you perform calculations to Excel date and time values.

Engineering

Let's you manipulate binary numbers, convert values between different numeral systems, and calculate imaginary numbers.

Financial

Calculate present value, interest, accumulated interest, principal, accumulated principal, depreciation, payment, price, growth, yield for securities, and other financial calculations.

Information

Functions that let you get information from a cell, formatting, formula, worksheet, workbook, filepath, and other entitites.

Logical

Functions that let you return and manipulate logical values, and also control formula calculations based on logical expressions.

Lookup and reference

These functions let you sort, lookup, get external data like stock quotes, filter values based a condition or criteria, and get the relative position of a given value in a specific cell range. They also let you calculate row, column, and other properties of cell references.

Math and trigonometry

You will find functions in this category that calculates random values, round numerical values, create sequential numbers, trigonometry, and more.

Statistical

Calculate distributions, binomial distributions, exponential distribution, probabilities, variance, covariance, confidence interval, frequency, geometric mean, standard deviation, average, median, and other statistical metrics.

Text

Functions that let you manipulate text values, substitute strings, find string in value, extract a substring in a string, convert characters to ANSI code among other functions.

Web

Get data from the internet, extract data from an XML string and more.

Excel categories

Home page

Latest updated articles.

Excel Functions

Detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 600 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form