Mehedi Hasan
Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we need to abbreviate the days of the week in Excel to better represent the data. But there are no direct methods to do so. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you 5 suitable methods to abbreviate the days of the week in Excel. Hence, read through the article to learn more and save time.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
5 Suitable Ways to Abbreviate Days of the Week in Excel
In this tutorial, I will show you how to abbreviate the days of the week in Excel. Here, I have used 5 different examples to make you understand the scenario properly. For the purpose of demonstration, I have used the following sample dataset.
1. Apply TEXT Function to Abbreviate Days of the Week in Excel
Fortunately, there are some built-in functions in Excel. With those functions, we can perform a variety of activities. The Excel TEXT function is one of the useful functions. Using this function, you can extract the abbreviated weekdays of dates. By the same token, I am using the same dataset. Hence, I have attached the steps to extract the abbreviated weekdays with the TEXT function.
📌 Steps:
- First, select cell C5 and insert the following formula.
=TEXT(B5,"ddd")
- Last, press Enter and utilize the AutoFill tool for the entire column.
Read More: How to Convert Abbreviations to Words in Excel (2 Easy Methods)
2. Combine CHOOSE and WEEKDAY Functions to Get Days in Abbreviation
Afterward, the CHOOSE function will also help to withdraw the weekdays from a date. In addition, we also need the WEEKDAY function to take the number of weekdays. However, the CHOOSE function chooses the same match for the inserted date. Hence, follow the steps to get a proper idea.
📌 Steps:
- In the beginning, select cell C5 and write down the following formula.
=CHOOSE(WEEKDAY(B5),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
🔎 Formula Explanation:
Here, the WEEKDAY function returns the number of days of the week, and the CHOOSE function returns the abbreviated name according to the serial.
- In the end, hit the Enter key to get the final result.
Read More: How to Apply Abbreviation Using Formula in Excel
3. Abbreviate Days of the Week in Excel with Format Cells Feature
In addition, if you want to change the format of the date by extracting the day, you can simply use the Format Cells option. It is an easy and time-saving task. Hence, read through the below steps to do it.
📌 Steps:
- Initially, select the column of the dataset where you want the abbreviated weekdays.
- Then, right-click and select the Format Cells option.
- Next, from the Number option, select Custom.
- After that, type “DDD” in the Type box and press OK.
- Lastly, you will receive the days of the week in abbreviated form.
Read More: How to Abbreviate Numbers in Excel (3 Easy Methods)
4. Return Abbreviated Days by Joining SWITCH and WEEKDAY Functions
Furthermore, another function to extract a month from a date is the SWITCH function. However, you can get the number of days with the WEEKDAY function. After that, you can switch the abbreviated weekdays from the date. So, let’s take a look at the steps.
📌 Steps:
- First of all, click on cell C5 and insert the formula mentioned below.
=SWITCH(WEEKDAY(B5),1,"Sun",2,"Mon",3,"Tue",4,"Wed",5,"Thu",6,"Fri",7,"Sat")
🔎 Formula Explanation:
Here, the WEEKDAY function returns the number of days of the week, and the SWITCH function returns the abbreviated name taking the number mentioned in the formula serially.
- Last of all, press Enter button and apply the AutoFill tool to the whole column.
5. Use Pivot Table with Format DAX Function
Last but not least, you can extract the days of the week by using the Pivot Table. However, you have to create the pivot table from the dataset and then use it with the format DAX function. For the purpose of demonstration, I have used the same sample dataset. Hence, go through the following steps in order to complete the operation.
📌 Steps:
- Firstly, select the dataset and select the Pivot Table command from the Insert tab.
- Secondly, check the Existing Worksheet and Location from the dialog box.
- Thirdly, check the option Add this data to the Data Model and press OK.
- After some modifications, select a cell on the pivot table.
- Fourthly, right-click on Range and select Add Measure.
- After that, insert the following formula into the DAX formula editor and hit OK.
= CONCATENATEX ( Range, FORMAT ( Range[Dates], "ddd" ), ", " )
- Now, drag the Abbreviated Weekdays to Values in order to add the column of weekdays.
- At last, the final output will appear in the image below after performing some modifications.
Conclusion
These are all the steps you can follow to abbreviate the days of the week in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.
