Get the Day Name from the Date in Excel

by

Ever struggled to remember if your cousin’s birthday falls on a weekend? Or needed to plan a meeting and wondered what day of the week it was?

Excel’s got you covered!

With some simple formulas, you can convert those confusing dates into friendly day names, making it super easy to schedule events, sort out your plans, or just satisfy your curiosity.

In this article, I will show you how to get the day name from the date in Excel using straightforward formulas.

Method 1: Using the ‘TEXT’ function option

The TEXT function converts a numeric value into text with a specified format in Excel.

It’s handy when you want to display numbers in a specific way, like, in this case, I want to know what the day name is when we have the date.

For the tutorial purpose, I will use the below dataset to understand the method better:

Using this data set for reference

Here is the syntax of the formula:

=TEXT(B2, “ddd")

Below is the explanation of the formula:

Explanation of the formula

The output would be like as below:

The output after putting the formula

Please note you can change the format code as per your desire. If you wish to have the full name of the day, like Saturday, then we will make a slight change in the code. 

The second type of formula for a full name of the week

To remember the format code, refer to the table below:

“ddd”Sat, Sun, Mon, Tue, Wed, Thu, Fri
“dddd”Saturday, Sunday, Monday, Tuesday, Wednesday, Thursday, Friday
The output that looks like after the second version of the formula

Copy and paste the formula in the remaining cells- for abbreviated form

Copy and paste the formula in the remaining cell for abbreviated form

Or Copy and paste the formula in the remaining cells for full-form 

Copy and paste the formula for the full form of the week

Important Note: As a best practice, we should always save the output from the formula as ‘values’ to avoid any data loss in the future.

To do so, copy and paste the input from the formula as ‘Paste as Values’

Follow the steps below to convert the formula to values: 

Step 1: Copy the cell range where you have just applied the formula. In this case, it is C2 or column titled as ‘Day of the week’- either by CTRL +C or Copy icon from the ribbon as highlighted in the screenshot below:

Copy the cell range where you have applied the formula

Step 2: Click on the drop-down below the ‘Paste’ icon. It will display a lot of options to paste

Click on the drop down below the paste icon. It will show a lot of options to paste

Step 3: Select the option ‘values’ as highlighted below in the screenshot.

Select the option values as highlighted

And voila! The input is stored as Values in column C now. 

Refer to the screenshot below to check whether the formula is replaced with the cell input in Column C. The formula is replaced with the desired input.

Before: You can see the detailed formula in the formula bar

To cheque whether the Formula has been replaced by value or not

After: Formula is replaced by value

To cheque whether formula has been replaced by value or not
Also read: How to Remove Year from Date in Excel?

Method 2: Using ‘Format Cells’ Option

This is the easiest functionality to use as it does not require any formula for any coding skills.

It will convert your dates to days of the week while replacing the existing dates.

Follow the steps below to get the day name from the dates:

Step 1: Select the cells where you have the dates. In this example, we are going to refer to column B titled ‘Date’

Dataset for tutorial purposes

Step 2: Right-click the selection, and a new pop-up box will appear. Select ‘Format cell’ from the options

Alternatively, you can use CTRL+1 shortcut to open the same dialog box.

Select format cells from the options provided

Step 2: That will open a new dialog box. In that, under the ‘Number’ tab, you will see a header called ‘Category’. 

Select ‘Custom’ from the options under Category. Refer to the screenshot below:

Select customs from option under the category

Step 3: The Custom option will show many formatting options. Under ‘Type’, write ‘ddd’ for the abbreviated form of the week (eg, Sat) or ‘dddd’ for the full name of the week (eg, Saturday).

Click ‘OK’ to confirm the selection.

Type DDD for abbreviated form or DDDD for full form and then click ok to confirm the selection

As you have selected the entire range of cells, it will replace the dates with the day of the week.

Replace the dates with the days of the week

When you use Format cells, it only changes the display of the date in the cells. However, the date will be there in the underlying. It can be seen in the formula bar when a cell is selected.

By using format cell the date will be there in the underlying
Also read: How To Convert Dates Stored As Text to Dates in Excel

Method 3: Using WEEKDAY and CHOOSE formula 

Using the WEEKDAY and CHOOSE functions together in Excel can be extremely powerful in applying customized formatting based on the day of the week. 

We will be using the same dataset as before for tutorial purposes.

Using the same data set for tutorial purposes

The advantage of this method is if you would like to use your own interpretation of the day of the week for the corresponding date in Excel, this combination can prove to be very helpful in fulfilling that task.

Before going into the details of the formulas, let us first understand the two functions individually.

WEEKDAY Function:

The WEEKDAY function is used to get the day of the week as a number (1 to 7), where 1 represents Sunday and 7 represents Saturday.

The syntax of the weekday function is 

WEEKDAY(serial_number,[return_type])
  • In this, serial_number is a required parameter that represents the date of the day you are trying to find. In this example, it is the date as 01-01-2022, 01-01, 2023, 01-01-2024 etc.
  • Return_type is an optional parameter. It lets us decide where we can begin the week. The default setting is Sunday as Number 1 and Saturday as Number 7- as the week-end on Saturday. 

However, if we put 2 here, it would consider Monday as the start of the week and Sunday as the end of the week. And so forth. 

The CHOOSE function:

The CHOOSE function in Excel is used to select a value from a list of values based on the position of the index number.

For example, if value1 through value7 are the days of the week, CHOOSE returns one of the days when a number between 1 and 7 is used as index_num.

The syntax of the Choose is: 

CHOOSE(index_num, value1, [value2], ...)
  • Index_num: It is a required parameter as it specifies which value argument is selected. In our example, it will be a weekday that we will use as the Index number.
  • Value: from which CHOOSE selects a value or an action to perform based on index_num. in this case, it will choose value from the list of weekdays provided.

Now, let us put these two functions together:

Below is the formula that will give us the day name when we have the date.

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

The steps to follow the formula together are as follows:

  1. Click on the blank range of cells where you want the day of the week to be shown.
Select the range where you want the day of the week to be shown
  1. Copy and paste the formula shown above
Copy and paste the formula as shown
  1. Press Enter Key
  2. This should display the day of the week in the form provided as the value, corresponding to the date in B2.
Press enter key and the output will provide the day of the week
  1. Copy this formula to the remaining cells by pressing CTRL + C and CTRL + V

Method 4: Using VBA

If you want to very simple formula that gives the day name for a date, you can create your own using VBA.

To get the day name from a date in Excel using VBA, you can use the WeekdayName function in combination with the Weekday function.

The Weekday function returns a number corresponding to the day of the week for a given date, and the WeekdayName function converts this number into the actual name of the day. Here’s a simple VBA function to do this:

Function GetDayNameFromDate(aDate As Date) As String
    ' Return the name of the day for the given date
    GetDayNameFromDate = WeekdayName(Weekday(aDate))
End Function

In this function:

  • The function GetDayNameFromDate takes a Date as input (aDate).
  • The Weekday function is used to get the day number of aDate.
  • Then, WeekdayName converts this day number into the corresponding day name.
  • The function returns the day name as a string.

To use this function in Excel:

  • Open your Excel workbook and press Alt + F11 to open the VBA editor.
  • Insert a new module and paste the above function into it.
  • Close the VBA editor.
  • Back in Excel, you can use this function just like any other Excel function. For example, if you have a date in cell A1 and you want to get the day name, you would use =GetDayNameFromDate(A1) in another cell.
  • This custom function is helpful when you need to display or utilize the day name from a date in your Excel worksheets, especially in situations where you’re analyzing data based on days of the week.

In this article, I showed you four easy methods you can use to convert a date into the corresponding day name.

While this can easily be done using the TEXT function, I prefer the Format Cells method as it doesn’t change the dates but still displays the day name in the cell.

Any of the above-mentioned methods can be used based on your requirements and comfort.

Other articles you may also like:

Leave a Comment