Table of Contents
To convert a date to its corresponding day of the week in Excel, you can use the WEEKDAY function. This function returns a number representing the day of the week (1 for Sunday, 2 for Monday, etc.) for a specified date. You can also use the TEXT function to format the returned number into the actual day of the week (e.g. “Sunday”, “Monday”, etc.). This allows you to easily display the day of the week for any given date in your Excel spreadsheet.
Excel: Convert Date to Day of Week
You can use the following formulas to convert a date to the day of the week in Excel:
Formula 1: Convert Date to Abbreviated Day of Week (e.g. “Thu”)
=TEXT(A2, "ddd")
Formula 2: Convert Date to Full Name of Day of Week (e.g. “Thursday”)
=TEXT(A2, "dddd") Each formula assumes that the date of interest is located in cell A2.
The following examples show how to use each formula in practice with the following list of dates in Excel:
Example 1: Convert Date to Abbreviated Day of Week
To convert the date in cell A2 to the abbreviated day of the week, we can type the following formula into cell B2:
=TEXT(A2, "ddd")We can then click and drag this formula down to each remaining cell in column B:
Column B displays the abbreviated day of the week for each date in column A.
We can verify that the days of the week are correct by manually checking a calendar for one of the dates.
For example, we can check a calendar to see that the day of the week for the first date of 12/4/2022 is indeed a Sunday:
Example 2: Convert Date to Full Name of Day of Week
To convert the date in cell A2 to the full name of the day of the week, we can type the following formula into cell B2:
=TEXT(A2, "dddd")We can then click and drag this formula down to each remaining cell in column B:
Column B displays the full name of the day of the week for each date in column A.
The following tutorials explain how to perform other common tasks in Excel:
How to Convert Date of Birth to Age in Excel
How to Calculate the Number of Months Between Dates in Excel