Get Day Name from Date in Excel – 4 Easy Ways

While working in Excel, especially in reports or charts, we like to know the day for a date value i.e. Monday, Thursday or Friday, etc.

It is a routine task for Excel users that manage projects and schedules to know if the date is a weekday or weekend. For this reason, they need to convert dates into the day of the week and return the name in Excel.

In this tutorial, we are learning some of the easy ways to return a day name for the date for the following example dataset with dates:

Get the day name with Custom Number Format

The easiest way to convert a date into a day name is by changing the number format of the cells. And there are several ways to do it.

1. Select the data range containing dates and CTRL+1 on the keyboard to open the Format Cells dialogue box. Or, having the dates selected > go to home tab > move to Number group’s bottom-right corner > click dialogue box button. This will invoke the Format Cells dialogue box.

change number format to get weekday name from dates in excel

2. On the left in the dialogue box, select Custom. You now have a type field and several formatting examples under it.

3. To get the full weekday name for the date, type DDDD in the type input field. And press OK.

custom format code to convert dates to day names in excel

After the above steps, you will get the name of the day for each date.

result of custom number format applied to dates to get weekday

Although you will see only the names and not the date in numbers, we can still use these cells to make calculations on date values. Only the visual format of the date has been changed to show the name of the day but in actual it is still a numerical value. And you can confirm it by looking at the formula bar by after selecting any of the cells in the data range:

formatting only changes the appearance not the underlying date value

Similarly, if we like to get the name of the day in the short form from a date, we will use ddd instead of dddd in the type field as format code:

format code to get short day names from date data in excel

Just above the type input field, you can see the preview of your results with provided format code. So you can easily confirm if your values are formatting as you desire.

Convert date to day name with TEXT Function

If the situation demands a formulaic approach, we can use the TEXT function in Excel to get the day name from a date.

TEXT function allows you to change the way a numerical value displays by applying a format code just like the custom number format above. But unlike customer number formatting that only changes the appearance, we can use the result of the TEXT function more flexibly that we will also discuss.

Taking the same example, I am adding another column to the right of the dates column to show the name of the day for dates.

In cell B2 paste the following formula to get the day name for the date mentioned in cell A2 and double click the fill handle:


And if you like to show day names in short form, then replace the format code with “ddd” instead of “dddd”:

Excel TEXT function to convert date to day name with formula

Unlike custom number formatting approach in which we cannot use the formatted result, the output from TEXT function can be used as we desire.

For example, if we like to mention the date with the day in one cell, we can easily do it using TEXT function using the following formula which is simply not possible with custom number formatting:

=TEXT(A2,”dddd”)&”, “&TEXT(A2,”m/d/yyyy”)

get date and day name with EXCEL TEXT function in formula

Return the Day Name using CHOOSE and WEEKDAY Functions

Exploring the formula approach further, combo of CHOOSE and WEEKDAY function is yet another way to get the name of the day from a date.

Continuing the same example, we can use the following formula to get the names of the day for each date in the dataset:

using CHOOSE function to get day name from date values in Excel

In the above formula, WEEKDAY function is giving us the number of the weekday i.e. 1 being Monday, 2 being Tuesday and so on.

With CHOOSE function we can replace weekday numbers into day names. As we have given 7 options to choose from, if WEEKDAY function outputs 1, then “Mon” will be selected. And if WEEKDAY function results in 6 then “Sat” will be selected from the options mentioned in the formula.

This might look counter intuitive especially after the above two methods discussed that are way simpler and gets the job done. However, this approach is easier to adapt in real life situations. Lets take an example.

Say for a manager, each day of the week has a specific task that is repeated for a month as following:

  • Monday = Stakeholders meeting
  • Tuesday = Site visit
  • Wednesday = Budget approvals
  • Thursday = Factory visit
  • Friday = Makeup meetings
  • Saturday = Break
  • Sunday = Break

In the formula above, instead of using day names, we can have task names so that manager can know the task at hand for a certain date in his schedule. For that formula will be as following:

=CHOOSE(WEEKDAY(A2,11),"Stakeholders meeting", "Site visit", "Budget approvals","Factory visit","Makeup meetings","Break","Break")

And this is the result:

CHOOSE and WEEKDAY formula to get task names from dates

Get the Day Name from a date in Excel Power Query

Power Query is one of the best tool to get weekday names from dates in few clicks. That can also update dynamically if data changes.

1. Select the data range and convert it to a table range either by pressing CTRL+T or using Insert tab.

2. Having an active cell within the table > go to data tab > under get and transform data click From Table/Range > This will open a Power Query interface:

Excel Power Query to get day names from date

3. With Power Query interface opened > click the header of second column to select it > go to transform tab > Date & Time column group > click Date drop-down button > hover above Day > click Name of the day.

4. Double left-click on the header of the column > type “Day” on the keyboard and press Enter key to change the header.

5. Go to Home tab > click Close and Load drop-down button > click close and load to > from the dialogue box > click Existing worksheet > remove the contents of input bar > click the cell where you want the processed data to be inserted > press OK.

Now even if we change the dates, we just need to update the query to get us the names of the day for each date.

So we learnt four of the easiest ways to get the day names from dates in Excel.

Hope this tutorial has helped in learning new Excel tricks.

Also checkout the following Excel tutorials regarding date and time: