Google Sheets Day of the Week: Formulas and Formatting

To show the day of the week for a date in Google Sheets, enter =TEXT(A2,"dddd") in another cell, assuming the date is in A2; it returns the full weekday name, such as Monday. Use =TEXT(A2,"ddd") for an abbreviated name, or use the WEEKDAY function when you need a number rather than a name.

How the weekday formula works

TEXT converts a date value into text displayed in a pattern you specify. In its date pattern, dddd means the full weekday name, while ddd means the shortened name. The underlying date in A2 remains unchanged; the formula displays a text result in the cell containing the formula. That distinction matters if you later sort or calculate with the result.

For example, if A2 contains the date January 15, 2024, the formula with dddd returns Monday, and the version with ddd returns Mon. The pattern is case-insensitive in ordinary use, but writing it as shown makes it easy to recognize. The formula does not require you to type the weekday yourself, so it updates whenever the date in A2 changes.

To apply the formula to a list, put it beside the first date and fill it down. Select the formula cell and drag the small square at its lower-right corner down the column, or copy and paste it into the remaining rows. Sheets adjusts the cell reference as it fills: the next row will refer to A3, then A4, and so on.

Choose a weekday number with WEEKDAY

Use WEEKDAY when a calculation needs a numeric result, such as grouping records by weekday or setting up a chart. Its second argument controls which day counts as the beginning of the week. With dates in A2:A8 covering Monday, January 15, through Sunday, January 21, 2024, the formula below returns 1 for Monday, 2 for Tuesday, and continues through 7 for Sunday.

With the date in A2, enter =WEEKDAY(A2,2); for January 15, 2024, the result is 1. The value is a number, not a weekday label. If you want both a number and a readable name, you can keep the original date, use one helper column for the number, and another for the name.

=WEEKDAY(A2,1) numbers Sunday as 1, Monday as 2, and Saturday as 7.
=WEEKDAY(A2,2) numbers Monday as 1 and Sunday as 7. This is often convenient for a Monday-first workweek.
=WEEKDAY(A2,3) numbers Monday as 0 and Sunday as 6. This zero-based scheme can be useful in calculations or programming-style logic.

If you omit the second argument, Sheets uses the Sunday-first numbering represented by 1. Specify the argument instead of relying on the default: it makes the intended week convention visible to anyone reviewing the sheet and avoids a quiet one-day shift in later calculations.

Set the week to start on Monday or Sunday

The weekday name returned by TEXT does not depend on whether you consider Monday or Sunday the first day of the week. A name formula simply reports the date’s weekday. The start-day choice matters for numeric results, calendar layouts, and weekly summaries. For example, a Monday-first schedule can use type 2 in WEEKDAY, while a Sunday-first calendar can use type 1.

Google Sheets does not change the meaning of a date just because a calendar is arranged differently. If you build a seven-column calendar, choose the first weekday deliberately and make the headings match the formula convention. A mismatch can make dates appear under the wrong heading even though the dates themselves and their calculated weekday names are correct.

For ISO-style week numbering, use ISOWEEKNUM to get the week number, and remember that this is not the same as the weekday number. ISO weeks begin on Monday, and the first ISO week is defined by the week containing the year’s first Thursday. Consequently, the ISO week-year can differ from the calendar year for dates near New Year’s Day.

Display a weekday without a separate formula

If you want a date cell to look like a weekday but still behave as a date, change its number format rather than replacing it with a TEXT formula. Select the date cells, then choose Format > Number > Custom date and time. In the custom format controls, choose or enter a weekday pattern such as a full name or abbreviation, then apply it. The exact controls can vary as the Sheets interface changes, but the menu path is in the web app.

This method preserves the date value for date arithmetic, sorting, and charts. It only changes how the cell is displayed. By contrast, the result of TEXT is text, which is handy for labels but may not sort chronologically as dates do. A common arrangement is to retain the date in one column and show its weekday in a neighboring formula column.

Custom formatting also lets a table show both date and weekday, or only the weekday, while retaining the original date underneath. If you display only the weekday, include a full date elsewhere in the table: seeing Monday alone does not tell a reader which Monday it is.

Handle blank cells and fill formulas safely

When a column has empty rows, a basic weekday formula may display an unexpected result for a blank reference. A blank-safe version keeps the output empty until a date is entered. With dates in A2:A100 and the weekday results in B2:B100, enter =IF(A2="","",TEXT(A2,"dddd")) in B2, then fill it down. If A2 is blank, B2 stays blank; if A2 contains January 15, 2024, B2 displays Monday.

For a large sheet, check that the formula refers to the intended date column before filling it down. If the date is in column C rather than A, update the reference accordingly. To keep a reference fixed while copying across columns, use dollar signs, as in $A2; this locks the column while allowing the row number to change. Use A$2 to lock the row instead. These reference choices are useful in schedules and templates that copy formulas in more than one direction.

Common problems and how to fix them

The formula returns an error. Check that the referenced cell contains a date value, not a text string that only looks like a date. A value pasted from another system may be text, especially if its month and day order does not match the spreadsheet’s locale.
The weekday is one day off. If you used WEEKDAY, inspect its second argument. Sunday-first and Monday-first numbering assign different numbers to the same date. The name from TEXT is not shifted by that setting.
The name appears in another language. Date and number display can be affected by spreadsheet locale and formatting settings. Check File > Settings and the date format before assuming the formula is wrong. Locale also affects how typed dates are interpreted.
A formula is displayed instead of a result. Make sure the cell is not formatted as plain text, and check that the formula begins with an equals sign. Re-enter the formula after changing the cell format if needed.
Sorting puts weekday names in an unexpected order. Text labels sort alphabetically, not in calendar order. Sort by the original date or by a numeric weekday helper column using a consistent numbering convention.

For ambiguous dates, use the date picker or enter a date in a form Sheets recognizes under the spreadsheet’s locale. You can also construct a date from year, month, and day components when those components are in separate cells. Confirm the displayed date before relying on its weekday; a date parsed as January 3 instead of March 1 will naturally produce a different answer.

Useful ways to apply weekday results

Weekday formulas are useful for more than labeling a calendar. A project tracker can identify which weekday a deadline falls on, a sales table can compare activity by day, and a roster can help organize recurring shifts. For reports, preserve the actual date and use weekday information as a grouping or display field rather than treating a name alone as a unique date.

For instance, a business may want to compare Monday sales with Friday sales across several months. A weekday label can help group those records, while a date column preserves the chronological record. If you use numeric weekday values for a chart or summary, choose a numbering convention once and label it clearly. Monday=1 and Sunday=7 is often easy to read in workweek reports.

Weekday results do not determine whether a date is a public holiday or a working day. A Tuesday can still be a holiday, and a Saturday can be a scheduled workday. If a sheet needs to account for holidays or unusual work schedules, maintain a separate holiday or calendar list and build the relevant logic around that data.

Google Sheets compared with Excel

The core functions shown here, including TEXT and WEEKDAY, also exist in Microsoft Excel, and their common weekday patterns and numbering options are similar. The instructions in this page are for Google Sheets in a web browser. In Sheets, the custom date display is under Format > Number > Custom date and time; Excel uses its own number-format controls, so do not expect the same menu path.

Google Sheets settings and locale can affect displayed date formats and the language of date-related labels. Spreadsheet formulas may also use different argument separators in some locales. If a formula copied from a web page is rejected, check the sheet’s locale and formula conventions rather than changing the weekday logic. The examples here use commas, as in the standard English-language syntax.

Frequently Asked Questions

What formula gives the day name in Google Sheets?
With a date in A2, use =TEXT(A2,"dddd") for the full name or =TEXT(A2,"ddd") for an abbreviation. The cell must contain a date value that Sheets recognizes.

How do I get Monday as 1 in Google Sheets?
With the date in A2, use =WEEKDAY(A2,2). This returns 1 for Monday through 7 for Sunday. The second argument is what selects the Monday-first numbering.

Can I show the weekday and keep the cell as a date?
Yes. Select the date cells and choose Format > Number > Custom date and time, then apply a weekday display format. Unlike a TEXT formula, this changes the display while retaining the date value.

Why does Google Sheets show the wrong weekday?
Check whether the date was interpreted in the intended month/day order, whether it is a real date rather than text, and whether you are looking at a weekday number with the correct WEEKDAY type. A numbering convention changes the number, not the date’s actual weekday.

How can I fill the weekday formula down a column?
Enter the formula beside the first date, then drag the fill handle down or copy and paste the formula into the rows below. Sheets adjusts relative references for each row. For rows that may be empty, use an IF check so the result remains blank until a date is entered.

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.