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

When you’re search information successful Microsoft Excel, you tin return advantage of nan built-in day formatting. However, you whitethorn want to show nan days of nan week alternatively of nan nonstop dates themselves.

If you for illustration to spot Sunday alternatively of 1/1/23 aliases Saturday alternatively of 23-Dec-30, we’ll show you a fewer ways to person nan day to nan time of nan week successful Excel.

Change nan Cell Formatting

The easiest measurement to show weekday names alternatively of dates successful Excel is by changing nan compartment formatting. When information successful a compartment is formatted arsenic a date, you tin simply alteration that to a civilization format for nan time name. This allows you to support your days successful their existent location.

1. Select nan cell, column, aliases compartment scope containing nan dates you want to alteration by dragging your cursor done them.
2. Do 1 of nan following:
3. Right-click and take Format Cells.
4. Go to nan Home tab, unfastened nan Number Format drop-down box, and take More Number Formats.

5. When nan Format Cells dialog container appears, make judge you’re connected nan Number tab and take Custom connected nan left.

6. On nan right, participate “dddd” successful nan Type section to show nan afloat sanction of nan day. If you want to usage nan weekday’s three-letter abbreviation, participate “ddd” instead.

7. You’ll spot a sample of this format astatine nan apical of nan box. Select OK to prevention nan change.

You should past person your dates displayed arsenic days of nan week.

Tip: Check retired our how-to if you’d for illustration to sort by day values successful Excel too.

Use nan TEXT Function

If you’re acquainted pinch utilizing Excel formulas and don’t mind adding a caller column, you tin usage nan TEXT usability to show days alternatively of dates. This is useful if you scheme to support some nan weekday and nan day aliases conscionable delete nan original dates.

1. Go to nan first compartment successful nan caller column. You tin usage nan file correct adjacent to nan dates aliases different successful nan expanse per your preference.
2. Enter nan pursuing look replacing nan compartment reference B2 pinch nan compartment containing nan first day successful your list. Then, usage “dddd” for nan afloat time sanction aliases “ddd” for nan three-letter abbreviation, each must beryllium wrong quotation marks:

=TEXT(B2,”dddd”)

3. You tin past use autofill to transcript nan look down to nan remaining cells. Select nan compartment pinch nan look and resistance nan capable grip successful nan little correct area of nan compartment downward.

You’ll past person nan days of nan week for each of your dates successful a caller column.

Combine nan WEEKDAY and CHOOSE Functions

One much measurement to person a day to nan time of nan week successful Excel is pinch nan WEEKDAY and CHOOSE functions. While a lengthier process than changing nan compartment formatting and longer look than pinch nan TEXT function, it’s still different option. This method besides requires a caller file for nan results.

The WEEKDAY Function

The Excel WEEKDAY usability obtains nan time of nan week from a day but displays it arsenic a number. By default, 1 is for Sunday, 2 is for Monday, and truthful on. You would usage nan pursuing look to get nan time of nan week for nan day successful compartment B2.

=WEEKDAY(B2)

As you tin see, nan weekday for our day is simply a 2, which is Monday.

The CHOOSE Function

The Excel CHOOSE usability picks a worth from a database of options pinch nan first statement arsenic nan worth you want to prime and nan remaining arguments arsenic nan database of items. To get nan first time successful our database of weekdays, you would usage this formula:

=CHOOSE(1,”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)

The WEEKDAY and CHOOSE Functions

Now, you tin harvester these functions and their formulas to show nan weekday names for your dates. You simply insert nan WEEKDAY look arsenic nan first statement for nan CHOOSE look which picks nan time of nan week based connected nan day successful that cell.

Using our supra example, we switch 1 pinch (WEEKDAY(B2)) for illustration so:

=CHOOSE((WEEKDAY(B2)),”Sunday”,”Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”,”Saturday”)

Like pinch nan TEXT usability above, you tin past usage autofill to resistance nan look down to nan remaining cells to get nan remainder of nan time names for your dates.

What’s nan Day for That Date?

You tin ever popular retired a almanac to spot which time of nan week a definite day falls on. However, if you person a database of dates successful Excel, you person a fewer different ways to show nan days instead.

For related tutorials, look astatine how to subtract dates successful Microsoft Excel.