excel day of week by date

"How to Get Day of the Week from Date in Excel"

By Ashley Hernandez
AD

Ever found yourself stuck in Excel, needing to extract the day of the week from a date? You're not alone. This common task can often leave users scratching their heads. But fear not, with a few simple steps and Excel's powerful functions, you can easily find the day of the week given any date.

Whether you're analyzing sales data over time, planning events, or simply trying to automate a repetitive task, knowing how to extract the day of the week is a crucial skill. So, let's dive into the world of Excel and explore how to achieve this.

Understanding Excel's Text to Columns Tool

Before we delve into finding the day of the week, it's helpful to understand how to split dates into day, month, and year. Excel's Text to Columns tool is a powerful ally in this process.

AD
2 Ways To Display Weekday For A Date In Excel
2 Ways To Display Weekday For A Date In Excel
2021 Dated Weekly Planner Excel Template for Organized Weeks
2021 Dated Weekly Planner Excel Template for Organized Weeks
Return the day of the week that a date falls on with Excel’s TEXT function. 🗓
Return the day of the week that a date falls on with Excel’s TEXT function. 🗓

To use it, select the cells containing your dates, go to the 'Data' tab, and click on 'Text to Columns'. Then, choose 'Delimited', and proceed with the steps. This will split your dates into separate columns, making it easier to extract the day of the week.

Splitting Dates into Columns

Assuming your dates are in column A, you can use the TEXT function to split them. In cell B1, enter '=TEXT(A1, "dd")', and drag this down. This will give you the day of the month. Replacing 'dd' with 'mm' or 'yyyy' will give you the month or year, respectively.

two rows of numbers are shown with the same number in each row, and an arrow is
two rows of numbers are shown with the same number in each row, and an arrow is
the date and time table for an event is shown in this screenshote image
the date and time table for an event is shown in this screenshote image
How to Display Day of Week from Date in Excel (8 Ways) - ExcelDemy
How to Display Day of Week from Date in Excel (8 Ways) - ExcelDemy

For example, if your date is '2022-01-03', using the 'dd' parameter will result in '03', the 'mm' parameter will give you '01', and 'yyyy' will produce '2022'.

Adding a Column for the Day of the Week

Now that we have the individual components of the date, we can find the day of the week. In cell C1, enter '=TEXT(A1,"ddd")'. This will give you the day of the week as a three-letter abbreviation, e.g., 'Wed' for Wednesday.

AD

If you prefer a full day name, use 'dddd' instead of 'ddd'. So, in cell C1, entering '=TEXT(A1,"dddd")' will output 'Wednesday'.

Google Sheets Calendar
Google Sheets Calendar
How to Add the Day of the Week to a Date in Excel | Daves Computer Tips
How to Add the Day of the Week to a Date in Excel | Daves Computer Tips
Excel Tip
Excel Tip
an excel spreadsheet with the date and time tab open to show dates for each month
an excel spreadsheet with the date and time tab open to show dates for each month
Weekly Calendar Spreadsheet, Weekly Planner Excel, Daily Task Schedule, To-Do List Excel, Task Schedule Spreadsheet
Weekly Calendar Spreadsheet, Weekly Planner Excel, Daily Task Schedule, To-Do List Excel, Task Schedule Spreadsheet
Get next day of week
Get next day of week

Using the WEEKDAY Function

Another way to find the day of the week in Excel is by using the WEEKDAY function. This function returns a number representing the day of the week, where 1 represents Monday, 2 represents Tuesday, and so on until 7 which represents Sunday.

For instance, in cell C1, entering '=WEEKDAY(A1,2)' will give you the day of the week as a number, with Monday being 2. If you want the full day name like 'Monday', you can wrap this in the TEXT function, like '=TEXT(WEEKDAY(A1,2),"ddd")'.

AD

Converting to Full Day Names

If you want the full day name but the first letter of the day to be capitalized, use the PROPER function. In cell C1, enter '=PROPER(TEXT(WEEKDAY(A1,2),"ddd"))'. This will result in 'Mon', not 'mon'.

How to Create Year and School Calendar with Dynamic Date Markers » The Spreadsheet Page
How to Create Year and School Calendar with Dynamic Date Markers » The Spreadsheet Page

To convert this to full capitalization, like 'MONDAY', use the UPPER function: '=UPPER(PROPER(TEXT(WEEKDAY(A1,2),"ddd")))'.

And there you have it! You now know several methods to extract the day of the week from a date in Excel. Mastering these skills will help you work more efficiently and accurately. Now, go ahead and apply these newfound skills to your projects. Happy Excel-ing!

AD
AD