How to Show Dates as Days of the Week in Microsoft Excel

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.

  1. Select the cell, column, or cell range containing the dates you want to change by dragging your cursor through them.
  2. Do one of the following:
  3. Right-click and choose Format Cells.
  4. Go to the Home tab, open the Number Format drop-down box, and choose More Number Formats.
  1. When the Format Cells dialog box appears, make sure you’re on the Number tab and choose Custom on the left.
  1. On the right, enter “dddd” in the Type field to display the full name of the day. If you want to use the weekday’s three-letter abbreviation, enter “ddd” instead.
  1. You’ll see a sample of this format at the top of the box. Select OK to save the change.

You should then have your dates displayed as days of the week.

Tip: Check out our how-to if you’d like to sort by date values in Excel too.

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.

  1. Go to the first cell in the new column. You can use the column right next to the dates or another in the sheet per your preference.
  2. Enter the following formula replacing the cell reference B2 with the cell containing the first date in your list. Then, use “dddd” for the full day name or “ddd” for the three-letter abbreviation, each must be within quotation marks:

=TEXT(B2,”dddd”)

  1. You can then use autofill to copy the formula down to the remaining cells. Select the cell with the formula and drag the fill handle in the lower right corner of the cell downward.

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 WEEKDAY Function

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 CHOOSE Function

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”)

The WEEKDAY and CHOOSE Functions

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 replace 1 with (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 at how to subtract dates in Microsoft Excel.



Source : How to Show Dates as Days of the Week in Microsoft Excel

Comments

Post a Comment

Popular posts from this blog

How to Add or Remove a Secondary Axis in Microsoft Excel Charts

Image
When you have a chart with mixed data types or a dataset that varies broadly between series, consider using a secondary axis in Microsoft Excel. With it, you can display the data you need without overwhelming or confusing your viewers. When to Use a Secondary Axis As mentioned, if you have different data types or your series amounts widely vary, these are good times to use a secondary axis. For instance, you can see that the column chart below doesn’t accurately depict the visitors and sales in our dataset. There’s a large variance in the sales amounts, making it hard to get a decent picture of the number of visitors per month. Plus, the sales are in dollar amounts, whereas the visitors are basic number values . If your chart would benefit from a secondary axis like this one, here’s how to add one as well as remove a secondary axis later if you change your mind or your data. How to Add a Secondary Axis in Excel To add a secondary axis to your Excel chart, you’ll use the combo ...
Read more

How to Use and Customise the Status Bar in Microsoft Excel

Image
Excel’s status bar is one of the hidden gems (and an unsung hero) that makes it the default spreadsheet software you turn to. It quietly resides at the bottom of the Excel window, often overlooked but brimming with valuable information and customization options. In this article, we will look at it and show you how to take advantage of it. Excel Status Bar Default Options Even before you customize the Excel status bar, it’ll display some helpful information and give you access to some useful functionalities. The common Excel options you can find as default on the status bar are: Cell Mode Macro Recording Tool Accessibility Check Page Number Statistics/Mathematical Options Worksheet Views Zoom Options Let’s see what each of them does and how you can use them. Cell Mode At the leftmost side of the Excel status bar, you’ll find the cell mode indicator. It’ll inform you about the status of the currently selected cell. There are four different status modes: Ready . It ...
Read more

What Is Windows AI-Powered Copilot?

Image
Microsoft has released its brand-new artificial intelligence (AI) companion, Microsoft Copilot. Previously known as Bing Chat, Copilot has a series of new abilities that can help you get more done in less time. In this article, we’ll provide a brief description of each of Microsoft’s new AI copilots, then we’ll focus on the Windows 11 copilot and what it can do to boost your productivity. What Is Microsoft Copilot? Microsoft Copilot is an artificial intelligence chatbot you can ask to perform different tasks, including generating text and images and answering general questions. Microsoft’s plan is to have a Copilot in each of its apps to provide assistance and improve user experience across the Microsoft ecosystem. Here are the different Copilots that are currently available or slated for future release: Windows 11 Copilot: Available for free as a preview in North America, Asia, and South America, Windows 11 Copilot lets you perform tasks, get answers, and create AI-generate...
Read more