DISCOVER
×

How to Convert Date to Day of Week in Excel

Updated April 17, 2017

Microsoft Office Excel (Excel) is spreadsheet software that allows the user to create workbooks that analyse 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.

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.

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

Select "Format" from the menu.

Select "Cells" from the drop-down list.

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

Select "Custom" in the "Category" list.

Enter "dddd" in the "Type" field to display the date as day-of-week, with the day entirely spelt 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).

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

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

In "B2," enter the formula "=TEXT(A2,"dddd")" to display the date as day-of-week, with the day entirely spelt 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.

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

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.

Tip

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

Things You'll Need

  • Excel
Cite this Article A tool to create a citation to reference this article Cite this Article

About the Author

Christy Flora has been writing professionally for more than fifteen years after winning her first awards for writing in the early 1980's. With a degree in Education, specializing in Organizational Leadership, Flora is an experienced, and published, author and editor. As a writer Flora has worked as an instructional designer spanning the genres of informational, educational and technical.