How to Convert Date to Day of Week in Excel

Convert Replace Date with Day of Week
1. Open the Excel workbook in which you want to convert and replace the date data with day-of-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format.
2. Left click with the mouse to select the column or row that contains the range of date data.
3. Select 'Format' from the menu.
4. Select 'Cells' from the drop-down list.
5. Select the 'Number' tab on the 'Format Cells' pop-up window.
6. Select 'Custom' in the 'Category' list.
7. Enter 'dddd' in the 'Type' field to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).Enter 'ddd' in the 'Type' field to display the date as day-of-week, with the day abbreviated to standard three-letter abbreviation (Mon, for instance).
Convert Date to Day of Week, Without Replacing Initial Data
8. Open the Excel workbook in which you want to convert and replace the date data with day-of-the-week data. If this workbook is not available, open up a new file for practice. If using a practice workbook, enter sample date data in mm/dd/yyyy format. This article will presume that the date data is located in column 'A,' with the header 'Date' in cell 'A1.'
9. Determine where you want the day-of-week data located. Label that column header with DOW (cell 'B1,' for example).
10. In 'B2,' enter the formula '=TEXT(A2,'dddd')' to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).
Or, you can enter the formula '=TEXT(A2,'ddd')' to display the date as day-of-week, with the day abbreviated to standard three-letter abbreviation (Mon, for instance).
Press 'Enter' to move the cell cursor out of cell 'B2'.
This is with the understanding that cell 'A1' contains the label 'Date' and cell 'B1' contains the label 'Day of Week' and that the first date is located in cell 'A2' and the first cell to be populated with the day of the week is cell 'B2'. If your workbook is set up differently, adjust the location of the formula to the desired location of the day-of-week data and adjust the formula accordingly to refer to the correct cell.
11. Return the cell cursor to cell 'B2'. Select 'Edit' from the menu.
Select 'Copy' from the drop-down list.
12. With the cell cursor, select the entire range where you want the day-of-week data. Select 'Edit' from the menu, and select 'Paste' from the drop-down list; this will paste the formula that you entered in cell 'B2' through the entire range of cells.