How to Convert Date to Day of Week in Excel

Microsoft Office Excel (Excel) is spreadsheet software that allows the user to create workbooks that analyze information and perform calculations. Many uses of Excel incorporate date-specific information. In various analyses or calculations, it may be necessary for the user to know what the day of the week is, not just the date. This article will lead you through two ways of converting dates from a numerical format to a text, day of the week (DOW), format. One way will replace the existing dates, the other will enter the new information in a unique column.

...
Convert Date to Day of Week in Excel

Convert & Replace Date with Day of Week

Step

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.

Step

Left click with the mouse to select the column or row that contains the range of date data.

Step

Select "Format" from the menu.

Step

Select "Cells" from the drop-down list.

Step

Select the "Number" tab on the "Format Cells" pop-up window.

Step

Select "Custom" in the "Category" list.

Step

Enter "dddd" in the "Type" field to display the date as day-of-week, with the day entirely spelled out (Monday, for instance).

Step

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

Step

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."

Step

Determine where you want the day-of-week data located. Label that column header with DOW (cell "B1," for example).

Step

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.

Step

Return the cell cursor to cell "B2". Select "Edit" from the menu. Select "Copy" from the drop-down list.

Step

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.