5 Ways to Convert a Date to the Weekday Name in Google Sheets

This post is going to show you all the ways you can get the weekday name from a date in Google Sheets.

The benefit of having a column of dates is that you can extract each component of a date into separate columns.

If the date is in numeric format, the individual component that you extract will also carry a similar format. You can extract a day component and show its full name like Monday, Tuesday, Wednesday, etc.

You will see how you can get and convert a date to the weekday name using the following methods.

  • Get the weekday name using the TEXT function.
  • Get the weekday name using the QUERY function.
  • Get the weekday name using a Custom data and time format.
  • Get the weekday name using the WEEKDAY and CHOOSE function.
  • Get the weekday name using a custom Apps Script function.

Get your copy of the example workbook using the above link to follow along.

Convert a Date to the Weekday Name with the TEXT Function

One easy way you can extract the weekday name from a date is by using the TEXT function.

This will allow you to retain the date value as is while extracting the day name into a new cell.

The TEXT function will convert a number to a text value and apply a custom format.

= TEXT ( number, format )
  • number is the number you want to convert to a text value.
  • format is the string that defines the format to be applied.
= TEXT ( A2, "dddd" )
= ARRAYFORMULA ( TEXT ( A2:A6, "dddd" ) )

You can use any of these syntaxes to convert the date to a weekday name.

The dddd format string will cause the TEXT function to return the long weekday name such as Saturday, Sunday, Monday, etc. You can also use the ddd format string to return the abbreviated day name such as Sat, Sun, Mon, etc.

This formula will need to be copied down your entire column to return results for each row.

You can avoid copying and pasting formulas by wrapping the TEXT formula inside an ARRAYFORMULA to create a single formula that returns values for your entire column of dates.

When the TEXT function is inside the ARRAYFORMULA function, you can then reference a range such as A2:A6 for the number argument instead of a single cell. This will return a weekday name for each date in A2:A6.

Convert a Date to the Weekday Name with the QUERY Function

This method is very similar to the TEXT function method.

The QUERY function allows you to apply formatting to its results via the FORMAT clause and it uses the same formatting syntax as the TEXT function.

= QUERY ( data, query, [headers] )

The QUERY function takes a range of data and performs a query to return results.

Check out this complete guide to the QUERY function for further details.

= QUERY ( A2:A6, "format A 'dddd'" )

Use the QUERY syntax to convert the date to a weekday name. The simple query returns the dates in column A and applies a format to those dates.

The "format A 'dddd'" query will apply the dddd formatting the results which returns the long day name.

You can also return the short day name by using the "format A 'ddd'" query instead.

Convert a Date to the Weekday Name with Custom Date and Time Format

With Custom date and time formats, you can create custom formats for dates.

You can even format dates as the weekday name!

When you apply a custom format the date value remains but the user will see only the weekday name in the grid.

Before you apply custom formats, first you must select the cell or range of cells to which you want to apply the format.

When you have selected the cells, go to Custom date and time to open the Custom date and time formats dialogue box.

To access Custom date and time format, go to the Format menu, then choose the Number option and click on Custom date and time.

Use the arrows to access more options for each date component.

Delete the Month and Year components so that only the Day component remains.

Open the options for the Day component and select the Day as full name (Tuesday) option.

Click on Apply button when you’re done.

You have now converted your dates to a weekday name.

Convert a Date to the Weekday Name with WEEKDAY and CHOOSE Functions

= WEEKDAY ( date, [type] )

The WEEKDAY function takes a date argument and returns the day or weekday number component of the date.

The type argument of the function refers to the numbering system the function will use to represent the days of the week. There are inputs for the type you can use.

  • type = 1 means the day of the week count starts from Sunday. Sunday represents 1 through to Saturday represents 7.
  • type = 2 means the day of the week count starts from Monday. Monday represents 1 through to Sunday represents 7.
  • type = 3 means the day of the week count starts from Monday. Monday represents 0 through to Sunday represents 6.
= CHOOSE ( index, choice1, [choice2, …] )

With the CHOOSE function, you can enter a list of choices using the choice argument. You can enter up to 30 choices using this argument.

Each choice is indexed in ascending order. The first item in the choice argument carries an index of 1, the second carries an index of 2, and so on until it reaches the last item in the choice argument.

The index argument is used to return any item from the list of choices.

= CHOOSE ( WEEKDAY ( B2 ), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" )

Passing the WEEKDAY function into the index argument means the CHOOSE function will return the corresponding choice, or the weekday name, from the choice list.

= ARRAYFORMULA ( CHOOSE ( WEEKDAY ( A2:A6 ), "Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday" ) )

You may also want to wrap this formula inside an ARRAYFORMULA to return an array of results as the above formula.

Convert a Date to the Weekday Name with an Apps Script

App Scripts is a great tool available in Google Sheets that allows you to create your own custom functions for use in your workbook.

You can use this to build your own custom function in Google Sheets that will return the weekday name.

If you want to learn more about Apps Scripts check out the full guide to Apps Scripts in Google Sheets.

Go to the Extension menu and select the Apps Script option to open the editor to create your new custom function.

function WeekdayName(date) {
  var numWeek = date.getDay();
  switch (numWeek) {
    case 0: return "Sunday";
    case 1: return "Monday";
    case 2: return "Tuesday";
    case 3: return "Wednesday";
    case 4: return "Thursday";
    case 5: return "Friday";
    case 6: return "Saturday";
  };
};

The code converts a date into a weekday number then returns the corresponding name for the number.

Copy and paste the syntax above in your app script editor window and press the Save button. Then, refresh your spreadsheet.

The syntax creates a custom function named WeekdayName. The function takes a date as an argument.

When you use the function, it gets the day component of the date argument, and it uses the switch command to substitute the day number for the weekday name.

=WeekDayName(A2)

You will now be able to use the above formula to return the day name from any date in your workbook.

Conclusion

There are many reasons you will want your date to only reflect the weekday name and if you need this you can easily apply any of the methods discussed.

The Custom date and time formats override the values in a cell, which may render the method less favorable in certain situations.

The other methods can don’t have this downside and will extract the information into a new cell.

Do you have any other methods for getting the weekday name from a date? Let me know in the comments below.

About the Author

Oluwaseun Olatoye

Oluwaseun Olatoye

Oluwaseun is a business intelligence analyst with expertise in Google Sheets and SQL programming language. He has worked with various businesses to make data-driven decisions. He enjoys helping others learn and grow.

Related Articles

Comments

1 Comment

  1. sandeep

    Amazing stuff!

    Reply

Submit a Comment

Your email address will not be published.

Get the Latest Google Sheets Tips

Write For Us

Are you a tech enthusiast with a talent for writing great content? Come write for us!

Follow Us

Follow us on social media to stay up to date with the latest in Google Sheets!