How to Obtain Weekday Name from a Date in Excel (3 Easiest Ways)

Excel is powerful spreadsheet software that is used for data analysis and management. Excel is a great tool, mainly because it has so many different functions that can be used to manipulate your data. But the best part is that you can use Excel spreadsheets to create automated processes. One of the most common tasks that people perform using Excel is the creation of a report that shows the day names for each week. This is commonly seen in some companies where employees take turns in reporting for example on Fridays.

For that, we need to extract weekday names from a data set because we want to present weekdays in the excel dashboard., This article will provide you with an efficient way to obtain weekday names from a data set in an excel sheet for making various calculations on them.

Use Word, Excel, and PPT for FREE

Free Download

3 Easiest Ways to Get the Weekday Name

1. How to get a weekday name in excel from a date: Formatting method

Formatting method involves the use of different date formats and converting them back to the day of the week, there are many different formats available when it comes to writing what date it is , we can make our own custom one and convert it into the day

1. Select the cell having date input

2. Left-click and select the optionformat cell

3. Now select the option custom

4. And type dddd in the column

5. Click okay, and this will get converted to the day

2. How to get a weekday name in excel from a date: Text Function

The Text Function is one of the most useful functions that can be used in Excel. The Text Function allows you to convert text into a number, currency, date or time. The Text Function also enables you to apply formatting and conditional formatting to the converted text.

The syntax of text function is

Text (Value,Format)

Value represents the cell number which we want to convert into day

Here's how we do this

1. Name the text in which date is placed and type “dddd” as format in text function like the example given below.

2. This will automatically convert it to the day of that date as shown below

3. Now you know how to convert a date into day using text function

3. How to get a weekday name in excel from a date: WEEKDAY Function

The WEEKDAY function returns the name of the weekday that contains a given date.For example, to find out what day of the week a given date is on, use the following formula:

WEEKDAY (Value, number)

1. It functions same as the Text function but the change here is that we use number instead of using “dddd”

2. Select the cell you want your output as

3. Type the function.

4. And here the results we get is 1, as you see 1 stands for Sunday ,that means it was Sunday on that date.

Use WEEKDAY formula to find and filter weekdays and weekends

As a manager or a data analyst, if you are going to examine the performance of your teams over a long period, you need to differentiate weekends from weekdays. Working with a spreadsheet can help you separate the days easily with a simple formula.

Here is how you can use WEEKDAY Function to separate holidays easily:

Step 1: In your spreadsheet containing dates, move to the next empty column so you can enter all formulas easily.

Step 2: Now use the following combination of the WEEKDAY Function:

=IF(WEEKDAY(A3, 2)

This is how entering the advanced WEEKDAY function in your spreadsheet will look like:

Step 3: You will now see dates sorted as Weekdays and Weekends in your dataset.

Step 4: Now click on the little inverted triangle and choose the “Weekend” or “Workday” filter to sort your data accordingly.

Step 5: In this case, this is what applying the Workday filter shows:

How to Highlight Weekdays and Weekends in Excel

Another great use of excel day of week feature is that you can beautify your worksheet by assigning different colors to weekends and workdays. Doing so will help you spot a weekend easily and ensure that you can save a lot of time.

Here is how to get started with the process.

Step 1: Start by entering these two formulas in your WPS spreadsheet:

For weekdays:

=WEEKDAY($A2, 2)

For workdays:

=WEEKDAY($A2, 2)>5

Step 2: Select all the cells containing dates and click the Conditional Formatting button.

Step 3: From the dropdown of Conditional Formatting, click on the New Rule button to get started.

Step 4: Choose the Use a formula to determine which cells to format feature so you can assign different colors to weekdays and workdays.

Step 5: Now locate the “Format only cells with” box within the dropdown.

Step 6: Proceed by providing the ranges of workdays and weekdays.

Step 7: Now click on the Format button within your WPS spreadsheet.

Step 8: In the Format button, define the colors for workdays and weekdays and click on Patterns.

Step 9: Finally, click on the OK button twice so you can save the formatting rule.

Step 10: This is the final result you will get from formatting your spreadsheet:

How to Calculate Weekdays and Weekends in Excel

Another way of using excel day of week feature is finding out the total number of workdays and weekdays. Doing so can help you better measure the KPIs and ensure you can identify top performers easily.

Here is how to use this feature:

Step 1: Select two empty cells in your spreadsheet and enter these formulas:

In our case, the formula for weekends in D3 is:

=SUM(--(WEEKDAY(A3:A20, 2)>5))

And in D4, the formula will be the following for counting weekdays:

=SUM(--(WEEKDAY(A3:A20, 2)

Step 2: You will see these results by entering the weekend and workday formulas:

The good thing about this workday and weekend counting formula is that it is super easy to use. You can rely on it to see the sum of different days easily without wasting time and effort.

FAQs about Weekday Name in Excel

Q. What is the WEEKDAY Function in Excel?

Using the WEEKDAY function in Excel allows users to get an automatic response of the day of a week corresponding to a given date. By default formatting, Excel returns a number from 1 to 7, where 1 is Sunday, and 7 is Saturday.

Q. Why is the WEEKDAY function not working?

1. This is why the “weekday function not working” problem occurs:

2. You might get the #VALUE! error when you enter a non-numeric type of serial or return or the serial you provide is out of 1900 to 9999 range.

3. #NUM! error shows up if the return is out of the range of the spreadsheet.

Optimize Sheets and Do More with WPS Office

Using the excel day of week feature can help you sort your work progress according to your holidays. If you regularly use spreadsheets for your personal or professional needs and want to boost your productivity, you should consider using WPS Office to get things done faster.

WPS Office is an all-in-one suite that allows you to create documents, spreadsheets, slides, and PDFs. It is a free suite and provides better functionality when compared to the expensive office products available in the market.

You can start using WPS Office on ANY leading operating system in no time. Download WPS Office and say goodbye to all your documentation-related problems!