WEEKDAY in Google Sheets: Formula, Numbering, and Tips

In Google Sheets, use =WEEKDAY(A2,2) to return the weekday number for a date in cell A2, with Monday as 1 and Sunday as 7. The optional second argument controls the numbering scheme; the function returns a number, not a weekday name.

What the WEEKDAY function does

WEEKDAY converts a date into an integer that identifies its position in the week. It is useful for sorting or filtering dates by weekday, finding weekends, building schedules, and applying rules that depend on the day of the week. The date may be entered directly in the formula or, more practically, stored in a cell and referenced there.

For example, if A2 contains the actual date April 15, 2024, the default numbering gives Monday the value 2, because Sunday is counted as 1. Choosing the Monday-first system gives Monday the value 1 instead. The date remains the same; only the number assigned to its weekday changes.

WEEKDAY does not determine whether a date is a holiday, and its result does not say whether a business is open. It simply identifies the weekday according to the selected numbering system. You can combine it with other functions or rules to apply your own business calendar.

Syntax and numbering options

The function has the syntax =WEEKDAY(date,[type]). The date argument is required and should be a valid date value or a reference to a cell containing one. The type argument is optional; if omitted, Google Sheets uses type 1.

Type 1, the default: Sunday is 1, Monday is 2, and Saturday is 7.
Type 2: Monday is 1, Tuesday is 2, and Sunday is 7. This is often convenient for workweeks that begin on Monday.
Type 3: Monday is 0, Tuesday is 1, and Sunday is 6. This system is useful when you specifically need a zero-based weekday number.

For a date in A2, the formula =WEEKDAY(A2) uses type 1 automatically. If A2 is April 15, 2024, it returns 2. With that same date in A2, =WEEKDAY(A2,2) returns 1, while =WEEKDAY(A2,3) returns 0. Choose one system for a calculation and use it consistently: comparing a type 1 result with a type 2 result can lead to incorrect conditions.

Worked examples for dates and schedules

Suppose A2 contains Wednesday, April 17, 2024. With A2 holding that date, =WEEKDAY(A2,1) returns 4, because Wednesday is the fourth day when Sunday starts the count. With the same cell layout, =WEEKDAY(A2,2) returns 3, and =WEEKDAY(A2,3) returns 2. These results make it easier to check which numbering system a formula is using.

To create a weekday number for a whole list, enter a formula beside the first date and fill it down. If dates are in A2:A20, enter =WEEKDAY(A2,2) in B2, then copy or drag the fill handle down column B. Each row will refer to its corresponding date. The result is a numeric label, so it can be used in filters, charts, or other calculations.

If you need to count weekdays between two dates rather than label an individual date, Google Sheets has the separate NETWORKDAYS function. With a start date in A2, an end date in B2, and a list of holidays in H2:H10, use =NETWORKDAYS(A2,B2,H2:H10). If A2 is Monday, April 15, 2024, B2 is Friday, April 19, 2024, and none of the listed holidays fall in that period, the result is 5. NETWORKDAYS counts working days using a Monday-to-Friday workweek; WEEKDAY itself only returns a weekday number.

Returning a weekday name instead of a number

A common misunderstanding is that WEEKDAY will display text such as Monday. It does not: the function returns an integer. To show a full weekday name based on a date in A2, use the TEXT function instead. With April 15, 2024 in A2, =TEXT(A2,"dddd") displays Monday. With the same cell, =TEXT(A2,"ddd") displays an abbreviated name, such as Mon.

TEXT is useful when the result needs to be readable in a report, label, or message. However, its result is text, not a weekday number, so it is not interchangeable with WEEKDAY in numerical comparisons. If you want both, keep one column for the date, another for the numeric weekday, and a third for the displayed name.

You can also change how a date appears without changing its underlying value. Select the date cells and choose Format > Number > Custom date and time to select or build a display format that includes the weekday. Formatting changes the appearance of the cell; it does not turn the date into a different date or replace WEEKDAY’s numeric result.

Finding weekends and applying conditional formatting

For weekend checks, type 2 makes the condition especially easy to read: Saturday is 6 and Sunday is 7. With a date in A2, =WEEKDAY(A2,2)>5 returns TRUE for Saturday or Sunday and FALSE for Monday through Friday. This can be used in another formula or as the basis of a conditional-formatting rule.

To highlight weekend dates in a range, select the range, then choose Format > Conditional formatting. In the conditional-formatting panel, set the rule to use a custom formula and apply a weekday test that refers to the first date cell in the selected range. For example, if the selected range starts at A2, the rule formula is =WEEKDAY(A2,2)>5. Choose a formatting style and select Done. The reference should match the top-left cell of the selected range so the rule evaluates each date appropriately.

That test assumes weekends are Saturday and Sunday. Some organizations use different workweeks, so do not treat the WEEKDAY weekend test as a universal business-calendar rule. If your weekend days differ, adjust the logic to match the relevant schedule, or use a function or custom setup suited to that calendar.

Make sure Google Sheets recognizes the date

WEEKDAY needs a date value, not merely text that looks like a date. Dates entered into a cell are usually interpreted according to the spreadsheet’s locale. For instance, a numeric date such as 4/5/2024 can mean April 5 in one locale and May 4 in another. If the value is ambiguous, check the displayed date and the spreadsheet’s locale before relying on the result.

In Google Sheets, the locale is available under File > Settings. The locale affects how dates and numbers are interpreted and displayed. A cell can also be formatted as a date using Format > Number > Date, but formatting alone does not convert arbitrary text into a valid date. If a pasted value remains text, re-enter it in a recognizable date format or convert it with an appropriate date-parsing method for the data’s locale.

Other warning signs include an error such as #VALUE! or a result that seems to shift by one day. Check that the source cell contains the intended date, that the formula references the correct row, and that the type argument is the one you expect. A result of 1 is not always Sunday: it is Sunday with type 1, Monday with type 2, and Tuesday with type 3.

Choosing a numbering system and avoiding common mistakes

Type 2 is a practical choice for many work schedules because the weekdays run from 1 through 5 and the weekend falls at 6 and 7. Type 1 may be more suitable when a Sunday-first calendar or convention matters. Type 3 can help when zero-based indexing is useful, but remember that Monday—not Sunday—is zero. The right option depends on how the result will be interpreted downstream.

Use the same type in all related formulas, especially when comparing weekday numbers.
Do not assume the result is a date serial or a weekday name; it is an integer from the selected system.
Check whether source dates are genuine date values and not text pasted from another system.
For weekends, write down which days count as nonworking days before building a rule.
When a formula is copied down, verify its relative cell references and the first row of any conditional-formatting range.

Google Sheets formulas normally use commas between arguments, as in the examples here. Depending on the spreadsheet’s locale, a sheet may require semicolons as separators instead. If a formula is rejected despite having the right function and references, check the locale and use the separator the sheet expects.

Google Sheets and Excel differences

The basic WEEKDAY behavior and the three commonly used numbering systems described here are also familiar to Excel users, so a simple formula often transfers between the two programs. This page’s menu directions and examples are for Google Sheets in a web browser. Excel uses its own interface for date formatting and conditional formatting, so do not assume that a Google Sheets menu path exists in Excel.

When sharing a spreadsheet across programs, check both the formula result and the date display. Locale settings, date interpretation, and cell formatting can affect what a reader sees, even when the underlying weekday calculation is correct. For a workbook with business rules, document which numbering scheme the formulas use so another person can maintain it safely.

Frequently Asked Questions

What does WEEKDAY return in Google Sheets?
It returns an integer identifying the day of the week. The number depends on the optional type argument: by default Sunday is 1, while type 2 makes Monday 1.

How do I make Monday the first day of the week?
Add 2 as the second argument. If the date is in A2, =WEEKDAY(A2,2) returns 1 for Monday and 7 for Sunday.

How do I display Monday instead of a number?
Use TEXT with a weekday format. If A2 contains the date, =TEXT(A2,"dddd") displays the full weekday name; the result is text rather than a numeric weekday value.

Why does WEEKDAY return the wrong day number?
First check the type argument, because the same date has different numbers under types 1, 2, and 3. Then verify that the cell contains the intended date and that the spreadsheet’s locale has not interpreted an ambiguous date differently.

Can WEEKDAY tell me whether a date is a holiday?
No. It identifies the weekday only. Holiday-aware workday calculations require a holiday list and a suitable function, such as NETWORKDAYS for counting working days across a date range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.