While working in Excel, we like to know the day for a date value i.e. Monday, Thursday or Friday, etc. especially in reports or charts. To get day name from date, we have several methods including Excel formulas and Power Query.
In routine Excel users working on projects and schedules needs 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:
In this tutorial, we are learning some of the easiest ways to return a day name for the date. Here is the example dataset with dates:
Get day name from date 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.
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.
After the above steps, you will get the name of the day for each date.
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.
Remember, with this method we have only change the visual “appearance” or format of the date, where 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:
Similarly, we can get the name of the day in the short form e.g. Mon, Tue, Wed etc. For this use ddd instead of dddd in the type field as format code:
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
Excel Formula can help us get day name from date. TEXT function in Excel, among other functions, is the easiest to get the day name from a date.
TEXT function changes 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.
Paste the following formula in cell B2 to get day name from date in cell A2 and double click the fill handle:
=TEXT(A2,"dddd")
For short form, replace the format code with “ddd” instead of “dddd”:
=TEXT(A2,"ddd")
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, to mention the date with the day name in one cell, use following formula with TEXT Function:
=TEXT(A2,”dddd”)&”, “&TEXT(A2,”m/d/yyyy”)
Get Day Name from Date using CHOOSE and WEEKDAY Functions
Combination of CHOOSE and WEEKDAY function is yet another way to get the name of the day from a date.
Using the same example, following formula will get the names of the day for each date in the dataset:
=CHOOSE(WEEKDAY(A2,11),"Mon","Tue","Wed","Thu","Fri","Sat","Sun")
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:
Get Day Name from date in Excel with 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:
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: