Microsoft Excel logo on a green background

Do you have a date in your spreadsheet that you want to turn into the day of the week? If so, Microsoft Excel has multiple features and functions to help you do that. We’ll show you how to use them.

Ways to Find the Weekday From a Date in Excel

To find the day of the week from a date, you have basically three ways. The first method is to turn your date into the day of the week in a numerical form. In this method, Sunday is displayed as number 1, Monday is number 2, and so on. This is calculated with Excel’s WEEKDAY function.

The second method displays the actual name of the day of week, like Sunday, Monday, and so on. This method uses Excel’s TEXT function that converts your date to the day format.

The third method re-formats your actual dates and displays them as the names of the day of week, like Sunday, Monday, and so on. If you use this method, you do not need a separate column to display the days, as your existing date is overwritten.

Find the Day of the Week as a Number

To find the day of the week as a number, use Excel’s WEEKDAY function as follows. The count starts from Sunday, where it is numbered 1. However, you can make the function count from any chosen day (like Monday), as we’ll explain below.

Advertisement

To start, first, open your spreadsheet with Microsoft Excel. In the spreadsheet, make sure you have at least one date. Then select the cell in which you want to display the day of the week.

Select a cell in an Excel spreadsheet.

In the selected cell, type the following WEEKDAY function and press Enter. In this function, replace B2 with the cell where you have your date.

=WEEKDAY(B2)

Enter the WEEKDAY function.

If you’d like to use the date directly in the function, type the following WEEKDAY function with your date in it and press Enter. Keep double quotes around your date.

=WEEKDAY("24/5/2021")

Enter the WEEKDAY function with direct values.

And in the selected cell, you will see the day of the week for your date as a number.

The result of the WEEKDAY function.

To make the function count days so that Monday is number 1, modify the function as follows. The number 2 in the function argument tells it to start counting from Monday.

=WEEKDAY(B2,2)

Type the custom WEEKDAY function.

Here’s how your result now looks like:

The result of the custom WEEKDAY function.

To copy the function to other cells, drag downwards from the bottom-right corner of the cell where you typed the function.

Copy the WEEKDAY function.

RELATED: How to Sort by Date in Microsoft Excel

Find the Day of the Week as a Name

To display a date’s day of the week as the day name, like Monday, use Excel’s TEXT function. In this function, you tell Excel to format your date in the day format. You can choose the shortened day name (like Mon) or full day name (like Monday).

Advertisement

Start by opening your spreadsheet with Microsoft Excel. Then select the cell in which you want to display the day of week.

Click a cell in an Excel spreadsheet.

In the selected cell, type the following TEXT function and press Enter. In this function, replace B2 with the cell where your date is.

=TEXT(B2,"dddd")

Enter the TEXT function.

The selected cell will display the day of the week. If you’d prefer a shortened day name, like “Mon” for Monday, remove one d from the function argument as follows:

=TEXT(B2,"ddd")

Type the custom TEXT function.

And you now have the shortened day name in your selected cell.

The result of the custom TEXT function.

Depending on how you like to style your sheets, you may want to format your dates using periods in Excel.

Convert Dates to Days of the Week

To convert your dates to days of weeks without using a separate column, use Excel’s custom number format. This displays the day of week by its full or shortened name.

Advertisement

To do so, first, open your spreadsheet with Microsoft Excel. Then select the cells containing dates.

Select the date cells.

In Excel’s ribbon at the top, click the “Home” tab.

Click the "Home" tab in Excel.

In the “Home” tab, from the “Number” section, select “Number Format” (an arrow icon).

Select "Number Format" in the "Home" tab.

A “Format Cells” window will open. On this window, from the “Category” list on the left, select “Custom.”

Select "Custom" on the "Format Cells" window.

On the right, click the “Type” box and enter “dddd” (without quotes) for full day names (like Monday) or “ddd” for shortened day names (like Mon). Then, at the bottom, click “OK.”

Specify the date format.

Advertisement

Excel will turn your selected dates into days of the week.

Dates turned into days of week.

And that’s how you know what day it was on a specific date in Microsoft Excel. Very useful!


Did you know you can use Excel to calculate someone’s age, too? It’s equally easy to do that.

RELATED: How to Calculate Age in Microsoft Excel

Profile Photo for Mahesh Makvana Mahesh Makvana
Mahesh Makvana is a freelance tech writer who specializes in writing how-to guides. He has been writing tech tutorials for over a decade now. He’s written for some of the prominent tech sites including MakeUseOf, MakeTechEasier, and Online Tech Tips.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.