How to Change Date to The Day of Week in Excel

In excel, we can type a date properly if set cell format as ‘Date’. If there is a list includes multiple dates, is there any way to convert these dates to the days of week accordingly? Actually, there are some tricks to convert date to day of week simply, in this free tutorial we will provide you some little tricks to convert date, you can select one of them to help you to solve your problem.


There are some dates list in ‘Date’ column. Now let’s convert them to day of week and list the result in ‘Day of Week’ column.

How to Change Date to The Day of Week 1

Method 1: Convert Date to Day of Week by Formula

Step 1: In B2 enter the formula =TEXT (A1,”dddd”). TEXT function can change selected value to your expected format.

How to Change Date to The Day of Week 2

Step 2: Click Enter to get result. Verify that Tuesday is returned. In this step, if you apply the formula to convert date format for the first time, a warning message will pop up, just confirm and close it.

How to Change Date to The Day of Week 3

Step 3: Drag the fill handle down till reaching the end of the list. Verify that day of week is displayed properly for each date.

How to Change Date to The Day of Week 4

Method 2: Convert Date to Day of Week by Format Cells Feature

Refer to method1 we can see that through changing the format of cells, we can change date to day of week properly. So, we can also change cell format for selected values through excel Format Cells feature.

Step 1: Copy dates in A column to B column firstly. Then select B2:B6, right click to load options, select Format Cells in the last section.

How to Change Date to The Day of Week 5

Step 2: On Format Cells panel, under Number tab, select Custom in Category list, in Type textbox type ‘dddd’, after typing you can see in Sample section ‘Tuesday’ is displayed.

How to Change Date to The Day of Week 6

Step 3: Click OK and quit Format Cells. Verify that day of week is displayed in each cell properly.

How to Change Date to The Day of Week 4


Related Posts

Phone Number Format in Excel

Sometimes when we import phone numbers from external sources, the formatting of the phone numbers is removed. Even if in some situations the phone numbers are not imported from external resource, sometimes the phone numbers in worksheet are not displayed ...

Add Leading Zeros in Excel

To pad a number with zeros, you need to add leading spaces and/or carriers. For example, if the original digit contains 2 digits, three additional characters will be added on before it. If there are 3 digits in total, allocating ...

How to Auto Fill Weekdays or Weekends in Excel

Sometimes we may want to enter a sequence of days in excel for some purpose, and we can press Ctrl+; to insert current date into cell, and then drag the cell down to attach following days into other cells, then ...

How to Create Increment Number with Texts in Excel

Sometimes we want to fill 1,2,3,4,5… into each cell in a column, for implement this we can enter 1 in the first cell and then drag fill handle down to fill the following cells. But if there are some texts ...

How to Generate Random Time in Excel

In some special situations we may need to generate some random times in worksheet. We can enter time manually and one by one, but if we want to require a lot of random dates in excel worksheet, we will spend ...