How to Convert Date to Day of Week in Excel: 7 ways to do it

Are you struggling to convert dates into days of the week in Excel? Don’t worry! We’ve got you covered. In this blog post, we’ll walk you through five simple methods to transform your dates into days of the week. Whether you’re an Excel newbie or a seasoned pro, these steps will help you master this essential skill. So, let’s dive in!

To give you a perspective, we will be learning seven methods to do it.

Method 1: Using the TEXT Function

The TEXT function in Excel is used to convert a numeric value into text in a specific number format. In our case, we’re using it to convert a date into the day of the week.

Here’s a step-by-step guide:

1. Identify the cell with the date – First, you need to identify the cell that contains the date you want to convert. In our example, this is cell A2.
2. Type the formula – Click on the cell where you want the day of the week to appear. This could be any empty cell. In this cell, type the formula =TEXT(A2, “dddd”). Here, “A2” refers to the cell containing the date, and “dddd” is the format code that tells Excel to convert the date into the full name of the day of the week.
3. Press Enter – After typing the formula, press Enter. Excel will calculate the formula and display the result in the cell where you typed the formula.

In Excel, when you’re working with date and time functions, the “d”, “dd”, “ddd”, and “dddd” codes are used to represent days. Here’s what each of them means:Convert Date to Day of Week in Excel: 7

• “d”: This format code displays the day of the month as a number without a leading zero when the day is a single digit. For example, the 1st of January would be displayed as “1”.
• “dd”: This format code displays the day of the month as a number with a leading zero when the day is a single digit. For example, the 1st of January would be displayed as “01”.
• “ddd”: This displays the day of the week as a three-letter abbreviation. For example, Monday would be displayed as “Mon”.
• “dddd”: This displays the full name of the day of the week. For example, Monday would be displayed as “Monday”.

So, if you have a date in cell A2 and you want to display the day of the month without a leading zero, you would use the formula =TEXT(A2, “d”). If you want to display the day of the month with a leading zero, you would use the formula =TEXT(A2, “dd”).

Remember, Excel’s TEXT function is case-sensitive, so you need to use lowercase letters for these format codes.Convert Date to Day of Week in Excel

Method 2: Using the WEEKDAY Function

The WEEKDAY function can also be used to convert dates into days of the week.

1. Type =WEEKDAY(A2) in a new cell.
2. Press Enter. This will return a number between 1 and 7, representing the day of the week (1 for Sunday, 2 for Monday, and so on).
3. To convert this number into the actual day name, you can use the CHOOSE function: =CHOOSE(WEEKDAY(A2), “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”).

Method 3: Using the Format Cells Option

The Format Cells option in Excel is a versatile tool that allows you to change the appearance of your data without actually changing the data itself. When it comes to dates, you can use this feature to display the day of the week instead of the date. Here’s a detailed step-by-step guide:

1. Select the cell – First, click on the cell that contains the date you want to convert. This will highlight the cell.
2. Open the Format Cells dialog box – Right-click on the selected cell to open a context menu. From this menu, select ‘Format Cells’. This will open the Format Cells dialog box.
3. Select ‘Custom’ – In the Format Cells dialog box, you’ll see a list of categories on the left. Click on ‘Custom’. This will allow you to enter a custom format code.
4. Enter the format code – In the ‘Type’ field, enter ‘dddd’. This is the format code that tells Excel to display the full name of the day of the week. If you want to display the abbreviated three-letter name of the day, you can enter ‘ddd’ instead.
5. Click OK – After entering the format code, click OK to close the dialog box. The date in the selected cell will now be displayed as the day of the week.

Remember, the Format Cells option changes the display of the cell, not the underlying data. So, even though the cell now displays the day of the week, the original date is still stored in the cell. This means you can use the cell in date calculations as you normally would.

The Format Cells option is a powerful feature in Excel that can help you make your spreadsheets more readable and informative. With practice, you’ll find it’s a handy tool to have in your Excel toolkit.Convert Date to Day of Week in Excel: 7

Method 4: Using a VBA Macro

For those who are comfortable with VBA, a simple macro can be used to convert dates into days of the week.

1. Press Alt + F11 to open the VBA editor or click of “Developer” tab and “Visual Basic”
2. Click on ‘Insert’ and then ‘Module’ to create a new module.
3. Copy and paste the below code into the module.

Function DayOfWeek(myDate As Range) As String
    DayOfWeek = WeekdayName(Weekday(myDate.Value))
End Function

Now save and close the VBA and lets see how to apply in excel.

1. Type “=DayOfWeek(A2)” to select the cell A2 with our custom function.
2. Press Enter and drag down it for all dates.
3. The result is shown below

Method 5: Using the LET Function

The LET function, introduced in Excel 365, allows you to assign names to calculation results. This can make your formulas easier to read and write.

1. In a new cell, type =LET(day, WEEKDAY(A2), CHOOSE(day, “Sunday”, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”)).
2. Press Enter. This formula first calculates the weekday number of the date in A2 (with Sunday as 1 and Saturday as 7), assigns it to the name ‘day’, and then uses the CHOOSE function to convert this number into the corresponding day name.

Method 6: Using the SWITCH Function : Convert Date to Day of Week in Excel

The SWITCH function, also available in Excel 365, is another way to convert weekday numbers into day names.

1. Type =SWITCH(WEEKDAY(A2), 1, “Sunday”, 2, “Monday”, 3, “Tuesday”, 4, “Wednesday”, 5, “Thursday”, 6, “Friday”, 7, “Saturday”) in a new cell.
2. Press Enter. This formula uses the WEEKDAY function to calculate the weekday number of the date in A2, and then the SWITCH function to convert this number into the corresponding day name.

Method 7: Using POWER QUERY

Power Query is a powerful data transformation tool in Excel. It can also be used to convert dates into days of the week.Convert Date to Day of Week in Excel: 7

1. Select the column containing your dates.
2. Click on the ‘Data’ tab and then ‘From Table/Range’ to load your data into Power Query.
3. With your date column selected, go to the ‘Add Column’ tab and select ‘Date’ -> ‘Day’ -> ‘Name of Day’.
4. Click ‘Close & Load’. Your dates will now have a corresponding day of the week.

Conclusion

Converting dates to days of the week in Excel is a common task that can be accomplished in several ways. Whether you’re using the TEXT function, the WEEKDAY function, the Format Cells option, a VBA Macro, Power Query, or even creating your own custom Excel function, each method has its own advantages and is suited to different situations.

The key is to understand your specific needs and the version of Excel you’re using. With practice, you’ll find the method that suits you best and become proficient at transforming dates into days of the week.

Remember, Excel is a powerful tool, and mastering it can significantly increase your productivity and efficiency. Keep exploring, keep learning, and you’ll discover that Excel is capable of much more than you might think.

Now that you have learnt this concept of excel in details, check your knowledge with this quiz. Choose the correct option. Convert Date to Day of Week in Excel