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 & 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 khổng lồ 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, và analyzing the data. To lớn work out the day of the week from a date, we will use functions & number formats explained in detail in the segments below. Let's get lớn 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 lớn 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 lớn the TEXT function:
Here are all these formats applied in the TEXT function lớn 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 & 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 & that's where the CHOOSE function comes in.
The CHOOSE function chooses a value from a danh mục of values based on an index number. See the connection here? That index number is going to lớn be supplied by the WEEKDAY function & then the CHOOSE function will choose its value from the các mục of values; the danh mục 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 & 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 khung a table of the list to be used within the function và 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 showroom the second argument to the WEEKDAY function. For more details we recommend you to lớn go through our WEEKDAY Function article here.
While the WEEKDAY & CHOOSE functions do the job, compared to lớn the TEXT function, they have too much to giảm giá 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 và much less complex with a shorter formula.
Using Format Cells Feature khổng lồ Convert a Date lớn 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 lớn 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 khổng lồ keep the date, you can paste the dates to lớn 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 khổng lồ the dates. Selecting any day will display its date in the Formula Bar.
Format lớn 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 vày it for you. If you are looking out to lớn just get the day name separately, one of the three options mentioned above is the way khổng lồ 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 lớn access the drop-down menu.Select the Long Date
Let's điện thoại tư vấn it a day now. We hope khổng lồ have given you some easy options for converting dates to week day names. Remember to lớn resort to the first three methods if you're aiming to lớn 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!
