How to Convert Date to Day of Week in Excel

By Christy Flora

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.

Things You'll Need

  • Excel

Convert & Replace Date with Day of Week

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

Step 2

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

Step 3

Select "Format" from the menu.

Step 4

Select "Cells" from the drop-down list.

Step 5

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

Step 6

Select "Custom" in the "Category" list.

Step 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

Step 1

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 2

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

Step 3

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 4

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

Step 5

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.

Tips & Warnings

  • Keyboard shortcut for copying cells and/or formulas: Ctrl+C
  • Keyboard shortcut for pasting data and/or formulas: Ctrl+V
  • Keyboard shortcut for opening "Format Cells" pop-up window: Alt+O, then E