This could especially be useful in project planning, where some days could be for a specific task (such as having a project meeting or sending the progress report), or where you need to know what days the working days are and what days are the weekends. In this tutorial, I will show you a couple of ways you can use to convert dates into the day of the week and get its name in Excel. So let’s get to it!
Get the Day Name Using Custom Number Formats
One of the best methods to convert a date into the day name is by changing the format of the cell that has the date. The good thing about this method is that while you see the day name in the cell, in the back end, it still continues to be the date. This way, you can still use the dates in calculations. Suppose you have a data set as shown below, where I have some dates in column A for which I want to know the name of the day. Below are the steps to do this: The above steps would convert the dates in the cells in column A and give you the name of the day for those dates. And as I mentioned, doing, this would only change the way the dates are being displayed in the cells. In the back end, the cells still contain the dates that can be used in calculations. When I use the custom code dddd, it tells Excel that I want to show only the day name from the date, and hide the month and the year value. Below are the formats you can use when you working with dates in Excel:
d – shows the day value from the date. If the day value is less than 10, only one digit is shown and if it’s 10 or more than 10, then two digits are shown.dd – shows the day value from the date in two digits. If the day value is less than 10, a leading zero is added to the number. For example, 5 would become 05ddd – this will show you the day name in the short format. If it’s Monday it would show you Mon, for Tuesday it will show Tue, and so on.dddd – when you use this custom format, it will show you the entire day name (such as Monday or Tuesday)
Note: For this method to work, you need to make sure that the dates are in a format that Excel understands as a date. For example, if you use Jan 21 2021, then it wouldn’t be converted into the day name as Excel doesn’t recognize this as a valid date format.
Get the Day Name Using TEXT Formula
You can also use the text formula in excel to convert a date into the name of the day. However, unlike the above custom number format method, the result of the text formula would be a text string. This means that you would not be able to use the result of the text formula in calculations as a numeric or date value. Let me show you how this method works. Suppose I have a dates dataset as shown below and I want the name of the days in column B. Below is the formula I can use to do this: The above formula will give you the full name of the day (such as Monday or Tuesday). All the custom number formats that are covered in the above section can also be used in the text formula. Just make sure that the format is within double quotes (as the one shown in the formula above). Now, you must be wondering why you would ever use the TEXT formula when the custom formatting method (covered before this method) gives you the same result and seems easier to use. Let me show you in what situation using the TEXT formula to get day names can be useful. Suppose I have the same data set as shown below, but now instead of getting the day name, I want to get an entire sentence where I have some text below or after the date name. Let’s say I want to get the result as ‘Due Date – Monday’ (i.e., I want to add some text before the day name) I can use the below formula to do this: Or, another example could be where you want to show whether the day is Weekday or Weekend followed by the day name. This can be done using the below formula: I’m sure you get the idea. The benefit of using the TEXT formula is that you can combine the result of that formula with other formulas (such as IF function or AND/OR function).
Get the Day Name Using CHOOSE and WORKDAY formula
And lastly, let me show you how to get the denim using a combination of CHOOSE and WORKDAY formulas. Below I have the data set where I have the dates in column A for which I want to get the day name. Here is the formula that will do this: In the above formula, I have used the WEEKDAY formula to get the weekday number of any given date. Since I’ve specified the second argument of the weekday formula as 2, it would give me 1 for Monday, 2 for Tuesday, and so on. This value is then used within the CHOOSE formula to get the day name (which is something that I have already specified in the formula). This is definitely bigger and more complex than the TEXT formula we used in the previous section. But there is one scenario where using this formula can be useful – when you want to get something specific to that day instead of getting the day name. For example, below is the same formula where I have changed the day name with specific activities that need to be done on those days. In the above formula, instead of using the day names, I have used the name of the activities that need to be done on those days. So these are three easy and simple ways you can use to convert a date value into the day name in Excel. I hope you found this tutorial helpful! Other Excel tutorials you may also like:
How to Get the First Day of the Month in Excel (Easy Formulas)How to Add or Subtract Days to a Date in ExcelHow to Calculate the Number of Days Between Two Dates in ExcelHow to Group Dates in Pivot Tables in Excel (by Years, Months, Weeks)How to Calculate Age in Excel using Formulas + FREE Calculator TemplateCheck IF a Date is Between Two Given Dates in ExcelHow to Autofill Only Weekday Dates in Excel (Formula)
