How to Convert Date to Day of Week in Excel

Written by christy flora
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Convert Date to Day of Week in Excel
Convert Date to Day of Week in Excel (Monday to Sunday monthly calendar, 2008 Year image by Stasys Eidiejus from Fotolia.com)

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.

Skill level:
Easy

Other People Are Reading

Things you need

  • Excel

Show MoreHide

Instructions

    Convert & Replace Date with Day of Week

  1. 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. 2

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

  3. 3

    Select "Format" from the menu.

  4. 4

    Select "Cells" from the drop-down list.

  5. 5

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

  6. 6

    Select "Custom" in the "Category" list.

  7. 7

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

    Convert Date to Day of Week, Without Replacing Initial Data

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

  2. 2

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

  3. 3

    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.

  4. 4

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

  5. 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 and 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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.