ExcelInsider Team
When working with Excel, users have a large amount of data to deal with on a regular basis. Managing project deadlines, analyzing data trends or keeping visitor records- whatever you do, you will need to deal with dates in Excel sheets. If you’re working with datasets that contain dates, it is often helpful to display day of the week from date. Excel provides several flexible and quick methods to extract and display the day of the week from a specific date.
➤ Create a new column B to display the days of the week.
➤ If the original date is in cell A2, type in cell B2:
=TEXT(A2, “dddd”
➤ Cell B2 will now display the day of the week that date in cell A2 represents.
➤ Hold and drag the formula down the column to apply it for all the dates.
In this article, we will show you all the methods in Excel to display day of week from date. Whether you prefer the built-in custom formatting method or using the Excel functions and formulas, we have included the detailed steps of every method. This complete guide will tremendously help you out while dealing with worksheets containing dates.
Use TEXT Function in Excel to Display Day of Week From Date
The TEXT function is the simplest and easiest way of displaying the day of the week from a particular date. The TEXT function in Excel converts a numeric value such as, a number or a date, into a text value. All you need to do is to create a separate column for the Day and apply the TEXT function properly in the cell. Here, we have a sample dataset containing the weather conditions for several dates.
We will use the TEXT function to display the day of the week from these dates following the steps below-
➤ Create a new column beside the dates column by right clicking on column B and pressing Insert. Name the new column B header as Day.
➤ Now, select cell B2 where you want to apply the first formula. To display the day of the first date in cell A2, type
➤ The day Monday for the first date 7/1/2024 will now be shown in cell B2. Hold the bottom right corner of cell B2 where the plus sign appears and drag it down to fill up the entire column.
➤ The column B will now display the days of the week for the respective dates in column A. You can just drag the formula down to apply it every time you enter new dates in the sheet.
Use WEEKDAY Function to Display Day of Week From Date
The WEEKDAY function in Excel is another convenient method to display the day of the week from the dates. This function can return a number between 1 to 7 from a date representing their position in the week. You can then convert these numbers into the names of the day easily.
In order to use the WEEKDAY function to do this job, follow these steps-
➤ Create a new column beside the dates column by right clicking on column B and pressing Insert. Name the new column B header as Day.
➤ Select cell B2. Click on the Insert Function (fx) box and a pop up box will open up.
➤ Click the downwards arrow beside the Select a Category drop-down box, and find the Date & Time option.
➤ Now, scroll down the Select a Function box and select the WEEKDAY function. Press OK when you’re done.
➤ In the Function Arguments box, select the Serial_number box and then select the cell A2.
➤ Since we want to count Sunday as number 1 and Saturday as number 7, we will choose the option 1 mentioned below. So, select the Return_type box and type 1 in it. You can choose any day of the week as number 1 here according to your preference. Then click OK.
➤ Hold the bottom right corner of cell B2 where the plus sign appears and drag it down to fill up the entire column.
➤ The entire Day column will now show the respective numbers for the day of the week the dates in column A represent.
➤ Now, to display these numbers as actual names of the day, right click on the cell B2 and choose Format Cells.
➤ In the Number tab, choose Custom. Then select the box under Type and write ddd in it. You will see the shortened version of the day of the week in the Sample box. For example, it will be shown as Mon for Monday.
➤ You can type dddd in the Type box to show the full form of the days of the week. Then click OK.
➤ Now, for the number 1, cell B2 will show Monday accordingly. Hold the bottom right corner of the cell B2 where the plus sign appears and drag it down to fill up the entire column.
Now, you can see the respective days of the weeks displayed for the given dates.
Apply The Custom Format to Display Day of Week From Date
This is another very easy method to display the day from a specific date. However, only using this method will replace the dates in the original column with the days instead. Therefore, you might lose records of the dates in the sheet this way. The Custom format method works this way-
➤ Select the cell A2 where the first date is located. Right click on it and select Format Cells from the options.
➤ In the Number tab, choose Custom. Then select the box under Type and write dddd in it. You will see the name of the day in the Sample box. Then press OK.
➤ Now, hold the bottom right corner of cell B2 where the plus sign appears and drag it down to fill up the entire column.
➤ Now, column A will show the respective days of the week for the dates that were mentioned there previously.
➤ As you can see, this has replaced the original dates in column A. If you want to preserve the original column as well, make a copy of the Date column in column B first and then apply this method in the same way in the new column B. The result will be like this:
Insert CHOOSE Function to Display Day of Week From Date
This method will be useful if you want to showcase the names of the day with some unique or different characters other than the form “Mon” or “Monday”. Suppose, you want Monday to be displayed as just “M” or “Mo” from the dates. In that case, the CHOOSE function will come in handy. This formula doesn’t act alone as it has the WEEKDAY function incorporated in it as well. Let’s take a look at how we can apply this method below:
➤ Create a new column beside the dates column by right clicking on column B and pressing Insert. Name the new column B header as Day.
➤ Select cell B2 and type
➤ You can see cell B2 will show the name of the day as Mo for Monday. Now, hold the bottom right corner of the cell B2 where the plus sign appears and drag it down to fill up the entire column.
➤ Column B will now display all the days as you wanted with their shortened versions. If you want it to show up as any other forms, simply replace the names of the day as you prefer in the CHOOSE function and it will work perfectly.
FAQs
How Can I Display Both the Date and the Day in One Cell?
You can display both the date and day in one cell instead of two separate columns by using the TEXT formula. In that case, your formula should follow this format: =TEXT(A2, “dddd, mmm dd, yyyy”). Here, the dddd is for displaying the day, mmm for the month, dd for the date, yyyy for the year.
What’s the difference between TEXT(A1, “ddd”) and TEXT(A1, “dddd”)?
You can use both these options in the TEXT formula to get different outputs. TEXT(A1, “ddd”) displays the day as the abbreviated version, such as Mon, Tues, Wed etc. On the other hand, TEXT(A1, “dddd”) displays the full name of the day such as Monday, Tuesday.
Does Excel Automatically Update the Weekday If The Date Is Edited or Changed?
If you are using an Excel formula such as the TEXT, WEEKDAY or CHOOSE formula, changing a date in the original cell will automatically update the day as well.
When Can I Use the WEEKDAY Function to Display Day from Dates?
The WEEKDAY function only returns a number representing the day of the week instead of its actual name. So, if you are okay with getting the day indirectly, you can use the WEEKDAY function without hesitation. Otherwise, you need to do an extra step and customize the names to be displayed instead of just numbers.
Wrapping Up
Knowing how to extract the days of the week from dates can undoubtedly save time and give your data more clarity. We have discussed simply custom formatting to display the days as well as using formulas such as- TEXT, WEEKDAY and CHOOSE. You can choose any of these methods that best suits your workflow and comfort. Let us know which method worked the best for you!
