Google Sheets Day of Week: Formula and Examples

To get a day-of-week number in Google Sheets, use WEEKDAY; for example, with a date in A2 and Monday numbered 1, enter =WEEKDAY(A2,2). To display the weekday as a name instead, use =TEXT(A2,"dddd"). These formulas answer two related but different questions: what number represents the weekday, and what is the weekday called?

Which Google Sheets function finds the day of the week?

WEEKDAY returns a number for the weekday of a date. Its first argument is the date to evaluate, and its optional second argument chooses the numbering system. If you leave out that second argument, Sheets uses a Sunday-first system: Sunday is 1, Monday is 2, and Saturday is 7.

That default is easy to overlook. If you want Monday to be the first day of the week, specify the second argument as 2. In that system, Monday is 1 and Sunday is 7. A third supported choice, 3, numbers Monday as 0 through Sunday as 6. Choose the system that suits the calculation or reporting convention you are using.

For example, with the date June 10, 2024, in A2, =WEEKDAY(A2,2) returns 1 because that date was a Monday. With the same date and =WEEKDAY(A2), the result is 2 because the default system counts Sunday as 1.

WEEKDAY and DAY are not the same

A common source of confusion is the function named DAY. It does not return the day of the week. It extracts the day of the month from a date: for June 10, its result is 10. WEEKDAY, by contrast, identifies the position of that date within the week.

Use DAY when you need a number from 1 to 31, such as the date on a monthly invoice. Use WEEKDAY when you need to identify Monday, Tuesday, or another weekday, or test whether a date falls on a weekend. Use TEXT when the result should be a readable weekday name rather than a number.

These functions do not change the date in the source cell. They calculate a result from it, so you can keep the original date for sorting and calculations while showing a weekday in a neighboring column.

Set up the date correctly

Before using a weekday formula, make sure the cell contains a date value that Sheets recognizes. A displayed date can look correct but be stored as text, particularly if it was pasted from another application or imported in a different regional format. Text that merely resembles a date may not behave like a spreadsheet date in calculations.

For a simple setup, put a date in A2 and the weekday formula in B2. With the date in A2 and a Monday-first numbering system required, enter =WEEKDAY(A2,2) in B2. The result for June 10, 2024, is 1. You can fill the formula down column B to calculate the weekday for each date in column A; each copied formula adjusts its row reference automatically.

To make a date rather than type one, with an empty A2 cell intended to hold June 10, 2024, enter =DATE(2024,6,10). The result is a valid date value, which other date functions can use. You can also type a date directly, then check whether Sheets recognizes it by changing its display format through Format > Number > Date.

If the date is being interpreted unexpectedly, check the spreadsheet’s locale under File > Settings. Locale affects how dates are interpreted and displayed; for example, a numeric date such as 04/05/2024 can mean different dates in different regional conventions. When sharing a sheet, an unambiguous date format or a clearly labeled column can prevent misunderstandings.

Show the weekday name with TEXT

For a label such as Monday or Tuesday, use the TEXT function with a date-format pattern. With a date in A2 and a full weekday name wanted in B2, enter =TEXT(A2,"dddd"). If A2 contains June 10, 2024, the result is Monday. The pattern ddd displays an abbreviated name, such as Mon.

The output from TEXT is text, not a date or a weekday number. That is useful for display, headings, and labels, but it matters if you plan to do further calculations. Keep the original date in its own column, and use the formatted name in another column when you need both a usable date and a readable label.

For a display-only alternative, you can format a date cell to show its weekday without adding a formula. Select the cells and choose Format > Number > Custom date and time, then choose or enter a weekday pattern such as dddd. The cell still contains its date value, even though it displays the weekday name. This is often preferable when you want a compact table but do not need a separate text result.

Choose the right numbering system

The second argument to WEEKDAY is important when the number will be used in later formulas. Pick one convention, use it consistently, and document it if other people will work with the sheet. A number such as 1 is ambiguous by itself: it could mean Sunday under the default system or Monday under the Monday-first system.

Omit the second argument: Sunday is 1, Monday is 2, and Saturday is 7. This is the default.
Use 2: Monday is 1, Tuesday is 2, and Sunday is 7. This is convenient for a Monday-to-Friday workweek.
Use 3: Monday is 0, Tuesday is 1, and Sunday is 6. This can be useful when a calculation expects zero-based weekday numbers.

For a weekend test based on the common Saturday-and-Sunday weekend, a Monday-first number makes the logic straightforward. With dates in A2:A100 and a status formula in B2 referring to the date in A2, enter =IF(WEEKDAY(A2,2)>5,"Weekend","Weekday") in B2 and fill it down. Saturday returns 6 and Sunday returns 7, so both are labeled Weekend. This example assumes those two days are the weekend; some workplaces and regions use different schedules.

Use weekday results in schedules and reports

Weekday calculations are useful wherever a date needs to be grouped or checked by day. A task list might include a weekday column so people can scan deadlines quickly. A bookings report could use weekday numbers to distinguish weekday traffic from weekend traffic. A calendar-like table can show the weekday next to each event date while retaining the original date for sorting.

For a quick visual check, you can highlight Saturday and Sunday dates with conditional formatting. With dates in A2:A100 and the conditional-formatting rule applied to that same range, choose Format > Conditional formatting, select a custom formula rule, and use =WEEKDAY(A2,2)>5. Set the desired formatting and apply the rule. The reference begins at A2 because that is the first cell in the selected range; Sheets adjusts it for the other cells.

For a summary, it is often better to keep the raw date and the weekday result in separate columns. That makes it easier to filter, sort, and check the data. If you use weekday names as text, remember that alphabetical sorting places Friday before Monday, not in calendar order. Sorting by the original dates or by weekday numbers using one consistent numbering scheme avoids that surprise.

Common problems and how to fix them

The result is one day off: Check which numbering system you selected. The default starts on Sunday; add 2 as the second argument for Monday-first numbering.
The formula returns an error: Confirm that the referenced cell contains a recognized date rather than text, and check that parentheses and commas are in place.
A date displays as a number: The underlying value may still be a valid date. Apply a date format through Format > Number > Date, or use a weekday display format.
The displayed weekday seems wrong: Check the date itself and the spreadsheet locale. A text date interpreted in the wrong regional order can refer to a different day.
The weekday name is in an unexpected language: Display conventions can depend on spreadsheet locale. Review the locale in File > Settings and verify how the sheet is intended to be shared.

Also remember that changing the appearance of a cell does not necessarily change its value. A date formatted to display as “Monday” remains a date, while the result of TEXT is text. This distinction explains why a formatted date can still be sorted chronologically, whereas a column of weekday-name text may sort alphabetically.

Google Sheets compared with Excel

The core functions covered here are also available in Excel: WEEKDAY, TEXT, and DATE use the same basic approach for these examples. The main differences are in the application interface and some locale or formatting behavior, not the weekday calculation itself. This page’s menu paths refer to Google Sheets in a web browser, not Excel’s ribbon.

Both spreadsheet programs store dates as values that can be used in calculations, and both can display a date in a different format from the value’s underlying meaning. When moving a file between Sheets and Excel, check the date interpretation, formatting, and weekday numbering convention rather than assuming that a displayed number has the same meaning in every formula.

Frequently Asked Questions

What is the Google Sheets formula for the day of the week?
Use WEEKDAY, such as =WEEKDAY(A2,2) for a date in A2 with Monday numbered 1. The default, =WEEKDAY(A2), numbers Sunday as 1.

How do I return Monday instead of a number?
With the date in A2, use =TEXT(A2,"dddd") to return the full weekday name, or use the custom date format dddd if you only want to change how the date appears.

Why does DAY return the date number instead of the weekday?
DAY extracts the day of the month, such as 10 from June 10. Use WEEKDAY to get a number representing the day of the week.

How can I check whether a date is on a weekend?
With a date in A2 and Saturday and Sunday treated as the weekend, test whether WEEKDAY(A2,2) is greater than 5. That Monday-first system assigns Saturday 6 and Sunday 7.

Can Sheets show the weekday without another formula column?
Yes. Select the date cells and use Format > Number > Custom date and time to apply a weekday display pattern. The cells remain dates, even when only the weekday name is visible.

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.