Is it a weekday or weekend? Will we be partying out late on New Year's Eve during the weekend or preparing to bustle to our schools and offices the next day? Are you closed for business on a certain holiday? What day would that be? For all serious and non-serious reasons, this tutorial will show you how to convert a date into its corresponding day of the week.
Bạn đang xem: Weekday formula in excel to get day of week, weekends and workdays
If your data is blazing with dates (this is especially true for daily planners, reports, etc.), it may be helpful to know the day of the week for grouping, organizing, and analyzing the data. To work out the day of the week from a date, we will use functions and number formats explained in detail in the segments below. Let's get to it.
Table of Contents
Using TEXT Function
Our first candidate for the job is the TEXT function. The TEXT function converts a value to text in the specified number format, which suits our objective perfectly. We will supply the date to the TEXT function. The format for the result will be the day name. Let's put everything to work.Here's the formula we will apply:
=TEXT(B3,"dddd")
The value that the TEXT function needs to convert into text is the date in cell B3. For the full name of the day of the week, we have enclosed dddd in double-quotes. You can pass other formats to the TEXT function:
Here are all these formats applied in the TEXT function to arrive at a single date or the day of the week from a complete date:
Child's play, isn't it?
Using WEEKDAY & CHOOSE Functions
The WEEKDAY and CHOOSE functions can be used together to get the day name from a date. By the looks of it, the WEEKDAY function should be enough for the purpose so what is the CHOOSE function doing? We believe some intros are due here.
The WEEKDAY function returns a number from 1 to 7, identifying the day of the week from a given date. But we don't want a number right now, we want the full day name and that's where the CHOOSE function comes in.
The CHOOSE function chooses a value from a list of values based on an index number. See the connection here? That index number is going to be supplied by the WEEKDAY function and then the CHOOSE function will choose its value from the list of values; the list of values being the days of the week.
Let's give you some practical outlook on how this works. Have a look at the formula before we explain what is going on:
=CHOOSE((WEEKDAY(B12)),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")
This is the formula we have used in the cells C3 and C4:
In the first instance at cell C3 first off, the WEEKDAY function is supplied with the date. The WEEKDAY function returns 6 which indicates Friday. Unless defined otherwise, the WEEKDAY function inherently counts the week from Sunday. WEEKDAY's result is passed onto the CHOOSE function which picks out the day from the supplied list. The 6th value is "Friday" which is the final outcome.
Xem thêm: Hướng Dẫn Tạo Tài Khoản Icloud Tren Android Đơn Giản, Miễn Phí
If you find inserting so much data into the CHOOSE function tiresome with all the double quotes, you can form a table of the list to be used within the function and supply the CHOOSE function with the cell references from the table. Having a separate table with the values also notably shrinks the whole formula.
Note: Since we have used the WEEKDAY function in its default form so it counts the week starting from Sunday. This is why we have listed the days in the CHOOSE function starting from Sunday. If you want the week to start from Monday or any other day for that matter, you need to add the second argument to the WEEKDAY function. For more details we recommend you to go through our WEEKDAY Function article here.
While the WEEKDAY and CHOOSE functions do the job, compared to the TEXT function, they have too much to deal with; the first indicator being that two functions are being used instead of one. For this purpose of getting the day name from a date, we find the TEXT function far easier and much less complex with a shorter formula.
Using Format Cells Feature To Convert a Date to Day Of Week
This option entails changing the format of the date, no function or formula is required. Though you will still be able to see the date in the Formula Bar when you select the cell, on the face of it, the day name will replace the date. If you still want to keep the date, you can paste the dates to another column and then change the format in the new column. Let's show you the steps to do this:
Paste all the dates to a new column.
And then we will have the days corresponding to the dates. Selecting any day will display its date in the Formula Bar.
Format to Date With Day Of Week Using Long Date Format
This option, like the previous one, requires no formulas or functions and is achievable in a few clicks. If you are comfortable with the format (i.e. dddd, mmmm dd, yyyy) then the Long Date format should do it for you. If you are looking out to just get the day name separately, one of the three options mentioned above is the way to go. If you decide on having the long date displayed, here's how:
Select the dates.Go to Home tab > Number group. Click on the little arrow on the number bar to access the drop-down menu.Select the Long Date
Let's call it a day now. We hope to have given you some easy options for converting dates to week day names. Remember to resort to the first three methods if you're aiming to have the days of the week identified separately. If you're okay with the Long Date format which contains the day and complete date, the final segment of this guide is your go-to. We'll head back with another piece of the Excel puzzle soon! Let's keep building and learning!
