How to Show Dates as Days of the Week in Microsoft Excel
Can be useful in certain situations
When you’re tracking data in Microsoft Excel, you can take advantage of the built-in date formatting. However, you may want to display the days of the week instead of the exact dates themselves.
If you prefer to see Sunday instead of 1/1/23 or Saturday instead of 23-Dec-30, we’ll show you a few ways to convert the date to the day of the week in Excel.
Change the Cell Formatting
The easiest way to display weekday names instead of dates in Excel is by changing the cell formatting. When data in a cell is formatted as a date, you can simply change that to a custom format for the day name. This allows you to keep your days in their current location.
You should then have your dates displayed as days of the week.
Tip: Check out our how-to if you’d like tosort by date values in Exceltoo.
Use the TEXT Function
If you’re familiar with using Excel formulas and don’t mind adding a new column, you can use the TEXT function to display days instead of dates. This is handy if you plan to keep both the weekday and the date or just delete the original dates.
=TEXT(B2,”dddd”)
You’ll then have the days of the week for each of your dates in a new column.
Combine the WEEKDAY and CHOOSE Functions
One more way to convert a date to the day of the week in Excel is with the WEEKDAY and CHOOSE functions. While a lengthier process than changing the cell formatting and longer formula than with the TEXT function, it’s still another option. This method also requires a new column for the results.
The Excel WEEKDAY function obtains the day of the week from a date but displays it as a number. By default, 1 is for Sunday, 2 is for Monday, and so on. You would use the following formula to get the day of the week for the date in cell B2.
=WEEKDAY(B2)
As you can see, the weekday for our date is a 2, which is Monday.
The Excel CHOOSE function picks a value from a list of options with the first argument as the value you want to select and the remaining arguments as the list of items. To obtain the first day in our list of weekdays, you would use this formula:
=CHOOSE(1,”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)
Now, you can combine these functions and their formulas to display the weekday names for your dates. You simply insert the WEEKDAY formula as the first argument for the CHOOSE formula which picks the day of the week based on the date in that cell.
Using our above example, we replace1with(WEEKDAY(B2))like so:
=CHOOSE((WEEKDAY(B2)),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)
Like with the TEXT function above, you can then use autofill to drag the formula down to the remaining cells to get the rest of the day names for your dates.
What’s the Day for That Date?
You can always pop out a calendar to see which day of the week a certain date falls on. However, if you have a list of dates in Excel, you have a few different ways to display the days instead.
For related tutorials, look athow to subtract dates in Microsoft Excel.
Sandy Writtenhouse is a freelance technology writer and former Project, Department, and Program Manager. She turned her education, job experience, and love of technology into a full-time writing career. With all sorts of gadgets in her home and her hands, she seeks to help others make life easier through technology.Read Sandy’s Full Bio
Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.
HomeAbout UsEditorial StandardsContact UsTerms of Use
Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved
