How to Get Day Name from Date in ExcelFormulas Included with Examples and More!

Written By:
Lisa Borga

Excel is a great tool for planning and storing records, and one thing both of these tasks often hold in common is working with dates.

Often when you work with dates, you need to know what day of the week a date is.

Particularly, when it comes to planning for projects knowing the difference between a workday and a weekend can help greatly.

It can also help to ensure that specific tasks can be allotted to particular days.

No matter what your reason for using it is, knowing how to convert dates into specific days of the week in Excel can be a useful skill.

Here we will show you a few ways that you can get this job done in only a few simple steps.

Convert Dates to Day Names With Custom Number Formatting

One of the simplest methods for converting a date into a day of the week is to change the formatting of the cell containing the date.

One of the best parts about this is that though the cell will display a day name, the actual date is still available in the back end, which means it can still be used for calculations.

To use this method to convert dates to days of the week, follow these steps.

  1. Select all of the cells that contain dates you would like to convert into days of the week.
  2. Right-click on the selected cells and select “Format Cells” This will open the “Format Cells” dialog box, which can also be accessed through the small arrow on the bottom right-hand of the “Number” group of the “Home” tab.

  1. In the dialog box, navigate to the “Number” tab, and under “Category” on the left side, select “Custom.”

  1. In the formatting options on the right side, select the “Type:” box, and here you can input unique formatting codes to choose how your date will be displayed.
  2. There are two formatting codes you can choose from to display the day of the week in your cells. This includes “ddd” which will input the short form of the days of the week and “dddd” which will display the full-day names.

  1. Once you have finished entering the code, select “OK.”

Now you are finished, and your dates should appear as ordinary days of the week.

Keep in mind that this is just formatting, and your actual dates are still stored and available in the back end. You can simply return to the previous formatting to restore your date’s appearance.

Converting Dates to Days of the Week Using the TEXT Function

Like with most tasks in Excel, formulas can be a great tool for changing dates to days of the week.

In this case, the TEXT function can be used to get the job done quickly and easily. The syntax of the TEXT function is:

=TEXT(value, format_code)

  • Value: The value, in this case, will be the date, the cell reference containing the date, or the date serial number.
  • Format Code: The format code will be the date format code you will use to tell Excel how the date should look once converted.

The TEXT function will apply the specified format code which you provide to the specified date and return the text string with this format.

For instance, if you used the formula =TEXT(D2:D7, “dddd”) in a blank cell in our example above, it would provide the days of the week for each of the dates.

The date format code can be changed in the function to whatever you desire. Here are each of the format codes you can use to format a date as a day.

  • d – This will display the day as one or two digits, such as 2 or 20.
  • dd – This will represent the date with two digits, such as 01 or 29.
  • ddd – this will display an abbreviated day of the week such as Sun or Tue.
  • dddd – This will display the full day of the week, such as Sunday or Tuesday.

Here are the steps to follow to format dates as days with the TEXT function.

  1. Select a blank cell where the days will be displayed. In our case, we prepared a column next to our existing dates in E2 to E7.

  1. Enter the formula with your dates and chosen date format. For our example, we enter the formula =TEXT(D2:D7, “dddd”) to display our dates with full days of the week. If you would prefer to abbreviate the days of the week, use the date format code “ddd” instead.

  1. Run the formula, and Excel will display the dates in the chosen cells with the date format you entered.
  2. If you would prefer these results without your original dates, you can copy and then select “Paste Values” from the “Paste Options” on the ribbon to replace the original dates.

As you can see, this is a relatively easy way to display dates as days, and it offers a considerable degree of flexibility as well.

However, there is another combination of functions you can use that can sometimes offer an even greater degree of flexibility, and we will take a look at these next.

Displaying Days in Excel with the CHOOSE and WEEKDAY Functions

The CHOOSE and WEEKDAY functions may not be quite as user-friendly as the TEXT function, but they can be used to the same effect and offer even more flexibility in regards to the result you can display.

For instance, when planning for future events, you may prefer to display customized results instead of just the names of the week, such as Weekend, Meeting Day, or Picnic. The options are endless, and fortunately, it is not hard to understand how you can use these functions together.

The WEEKDAY function allows us to convert a specific date into a number from 1 to 7, corresponding to the days of the week. The CHOOSE function lets us specify the outcome we want based on a particular value, which in our case will be the days of the week.

When put together, functions enable us to create a formula that will determine the day of the week from a particular date and then name them accordingly. Here is how.

  1. Select a blank cell where you would like to display the results. In our case, cell E2.

  1. Now to change the formatting appropriately, we will need to enter the formula: =CHOOSE(WEEKDAY(D2:D7,1), “Sun”, “Mon”, “Tue”, “Wed”, “Thu”, “Fri”, “Sat”). In this formula, we have entered the abbreviated days of the week. After our cell references, we included a “1” in order to instruct Excel to count the days of the week starting from Sunday. If, instead, we wanted to count starting from Monday, we would simply enter a “2” instead.

  1. Now if we want to replace our original dates, we can simply copy the results and paste them over the original values using “Paste Values” from the “Paste Options” on the ribbon.

The advantage of using this method is that instead of simply using the names of the days, we can enter anything we want.

This means that we could substitute activities, events, or anything else in place of the days, which can be a big plus for organizing projects or planning.

Conclusion

Knowing how to convert dates to days in Excel can save a lot of time and make it far easier to plan ahead.

Here you have seen three quick and easy ways to convert date values into day names and the formulas you need to get the job done.