WEEKDAY function in Excel

WEEKDAY returns the day of the week of a date as a number.

WEEKDAY tells you which day of the week a date falls on, as a number. With it you can mark weekends, total sales by day of the week, find the Monday of any week, or check whether a holiday lands on a Sunday.

Syntax

=WEEKDAY(serial_number, [return_type])

Argument	What it is
`serial_number`	A date, or a cell that holds one.
`return_type`	Which day counts as 1. Left out or 1: Sunday is 1 and Saturday is 7. With 2: Monday is 1 and Sunday is 7. With 3: Monday is 0 and Sunday is 6.

Examples

Each result below is what Excel itself showed for the formula on the sample sheet.

Sample sheet: Staff with their date of birth and date of joining, and a short list of holidays.

	A	B	C	D
1	Name	Born	Joined	Holiday
2	Anita	19-Jul-1988	01-Apr-2019	26-Jan-2026
3	Rohit	30-Nov-1992	15-Jun-2020	04-Mar-2026
4	Deepa	29-Feb-1996	01-Sep-2021	15-Aug-2026
5	Manoj	05-Dec-1999	10-Jan-2022	02-Oct-2026

=WEEKDAY(D2)
Result: 2
26 January 2026 with the default numbering, where Sunday is 1. Monday is 2.
=WEEKDAY(D2,2)
Result: 1
The same date with return type 2, where Monday is 1.
=TEXT(D2,"dddd")
Result: Monday
For the name of the day, TEXT is simpler.
=WEEKDAY(D4,2)
Result: 6
15 August 2026 with Monday as 1: a 6 means Saturday.
=IF(WEEKDAY(D4,2)>5,"Weekend","Working day")
Result: Weekend
With Monday as 1, anything above 5 is Saturday or Sunday.
=D4-WEEKDAY(D4,2)+1
Result: 10-Aug-2026
The Monday of the week that contains the date.
=D4+8-WEEKDAY(D4,2)
Result: 17-Aug-2026
The next Monday after the date.
=SUMPRODUCT((WEEKDAY(D2:D5,2)>5)*1)
Result: 1
How many of the four holidays fall on a weekend.

The numbering

The second argument decides which day is number 1. It is worth setting it every time, because the default surprises people.

return_type	Sunday	Monday	Tuesday	Wednesday	Thursday	Friday	Saturday
1 or left out	1	2	3	4	5	6	7
2	7	1	2	3	4	5	6
3	6	0	1	2	3	4	5

Return type 2 is the most convenient. Monday to Friday are 1 to 5, and the weekend is 6 and 7, so "is it a weekend?" becomes simply WEEKDAY(date,2)>5.

Types 11 to 17 start the week on other days: 11 is the same as 2, 12 starts on Tuesday, and so on up to 17, which starts on Sunday.

The name of the day

WEEKDAY gives a number. For a name, TEXT is the direct route: =TEXT(A2,"dddd") gives Monday, and "ddd" gives Mon. Or simply format the date cell itself with the custom format dddd.

Marking weekends

A label: =IF(WEEKDAY(A2,2)>5,"Weekend","Working day")
Shading rows: in Conditional Formatting, use the formula rule =WEEKDAY($A2,2)>5.
Where only Sunday is a holiday: =WEEKDAY(A2,2)=7.

Totals by day of the week

Add a helper column with =WEEKDAY(A2,2) or =TEXT(A2,"ddd"), and total by it with SUMIF or a PivotTable. Shops use this to see which days are busiest. Without a helper column, SUMPRODUCT does it: =SUMPRODUCT((WEEKDAY(A2:A13,2)=6)*F2:F13) adds up Saturday sales.

Moving to a particular day

With return type 2, the weekday number is also "how many days into the week".

To get	Formula
Monday of the same week	`=A2-WEEKDAY(A2,2)+1`
Sunday at the end of the week	`=A2-WEEKDAY(A2,2)+7`
The following Monday	`=A2+8-WEEKDAY(A2,2)`
The previous Friday	`=A2-WEEKDAY(A2,2)-2`

Grouping dates by "week starting" uses the first of these as a helper column.

Working days

To count working days between two dates, or to find the date a number of working days ahead, do not build it from WEEKDAY. NETWORKDAYS and WORKDAY are made for that, and they also accept a list of holidays.

Common mistakes

Assuming Monday is 1 without giving the second argument.
#VALUE! when the date is stored as text.
An empty cell returns 7 with the default numbering, because day zero in Excel's calendar is treated as a Saturday. Blank rows can therefore be counted as Saturdays. Guard with IF(A2="","",...).
A result shown as a date in January 1900 because of leftover date formatting. Set the cell to General.

In Google Sheets

WEEKDAY is the same in Google Sheets, with return types 1, 2 and 3.

Related functions

Every result on this page was calculated in Microsoft Excel (version 16.0, build 20326) on 12 November 2026, not typed by hand.

This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.