Skip to main content

4 easy ways on finding day of week from any date

In our data-driven world, Excel remains a vital tool for organizing, analyzing, and visualizing information. One frequent use-case is determining the day of the week from a specific date. Whether you're scheduling events or analyzing trends based on days, knowing how to extract this information can be incredibly beneficial. Let's delve into four straightforward methods to achieve this in Excel.


Video: Find day of week from date

 


Get Day of Week from a Date in Excel

 

Here this tutorial will introduces 4 different ways on getting the day of the week from dates.


Using Format Cells: Directly modifies the original date to display the day of week

The Format Cells feature of Excel allows you to modify the dates to various formats including day of the week.

Step 1: Select dates and right click to choose Format Cells form contents menu

Step 2: Specify settings in Format Cells dialog

Under Number tab, select Custom , then go to the Type section, enter dddd into the textbox. Click OK.

Result:

doc get day of week 7

Note: If you want to show the dates as weekday in abbreviation, such as "Mon", in the step 2, type ddd into the textbox.

Using Kutools: Allows swift toggling between various date formats, including the week of day

Beyond simply formatting dates as days of the week, you might want to display them in other styles, like showcasing only the month or perhaps just the day and month. This is where the Apply Date Formatting feature of Kutools for Excel comes in handy. With it, you can easily transform dates into a variety of formats, no need to memorize any codes – just a single click does the trick.

After installing Kutools for Excel, select the cells you want to handle, click Kutools > Format > Apply Date Formatting, then choose the date format you need from Date format list, and preview the result in right section. And then click OK.

Result

Notes:
  • In addition to the default date formats provided, you can create custom date or time formats by clicking the Add button doc add for future use.
  • To use Apply Date Formatting feature, you need have Kutools for Excel installed. If you have not yet, download and try it today.

Using TEXT Function: Displays the day of the week in a separate cell

If you want to show dates as days of week in a separate cell without altering the original date, you can use the TEXT function.

The TEXT function converts a value to text with a specified format in Excel.
Step 1: Select a cell that will place the day of week, apply below formula, and press Enter key
=TEXT(A2,"dddd")
A2 is the cell reference to the date you want to get its day of week. "dddd" indicates to convert a date to text and display as a full name of the day of the week such as "Monday".

doc get day of week 3 1

Step 2:Drag fill handle down to cells for getting all results

fill 1

Note: If you want to convert a date to text and display it as the abbreviation of the day of the week, such as "Mon", in the formula, using "ddd" instead of "dddd".

Using WEEKDAY Function: Returns the day of the week as a number (1 for Sunday, 2 for Monday, etc.)

The WEEKDAY function in Excel provides the day of the week for a given date, represented as an integer. By default, this value ranges from 1 (indicating Sunday) to 7 (indicating Saturday).

Step 1: Select a cell that will place the day of week, apply below formula, and press Enter key
=WEEKDAY(A2,1)
A2 is the cell reference to the date you want to get its day of week. The number '1' specifies the return type (Day of the week, with numbers 1 (Sunday) through 7 (Saturday)). For a detailed understanding of the different return types, please refer to the table provided below.

Step 2:Drag fill handle down to cells for getting all results

Return type Number returned
1 Numbers 1 (Sunday) through 7 (Saturday)
2 Numbers 1 (Monday) through 7 (Sunday)
3 Numbers 0 (Monday) through 6 (Sunday)
11 Numbers 1 (Monday) through 7 (Sunday)
12 Numbers 1 (Tuesday) through 7 (Monday)
13 Numbers 1 (Wednesday) through 7 (Tuesday)
14 Numbers 1 (Thursday) through 7 (Wednesday)
15 Numbers 1 (Friday) through 7 (Thursday)
16 Numbers 1 (Saturday) through 7 (Friday)
17 Numbers 1 (Sunday) through 7 (Saturday)

Each of these methods has its unique advantages, depending on the specific needs of your Excel project. For more invaluable Excel tips and tricks that can transform your data processing, dive in here.


The Best Office Productivity Tools

Kutools for Excel - Helps You To Stand Out From Crowd

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

  • One second to switch between dozens of open documents!
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.