How to return a day of the week for a date in Excel

Article translations Article translations
Article ID: 827327 - View products that this article applies to.
Expand all | Collapse all

On This Page

SUMMARY

This article describes how to return a day of the week for a date that is contained in a cell in Microsoft Excel.

MORE INFORMATION

To return a day of the week for a date, use one of the following methods.

Note Assume that cell A1 of your worksheet contains the date that you want to reference.

Method 1

  • In Microsoft Office Excel 2007, follow these steps:
    1. In the cell that you want to return the day of the week, type =A1, and then press ENTER.
    2. Select the cell with the returned date.
    3. Click the Home tab.
    4. Click Format Cells: Number in the Number group. This will open the Format Cells dialog box.
    5. On the Number tab, click Date, and then select the date format that contains the day of the week in the Type list.
    6. Click OK to close the Format Cells dialog box.
  • In Microsoft Office Excel 2003 and earlier versions of Excel, follow these steps:
    1. In the cell that you want to return the day of the week, type =A1, and then press ENTER.
    2. Select the cell that has the returned date.
    3. On the Format menu, click Cells.
    4. On the Number tab, click Date, and then select the date format that contains the day of the week in the Type list.
    5. Click OK to close the Format Cells dialog box.

Method 2

If you want to return the day of the week as an abbreviation (for example, as Wed), type the following in the cell that you want to return the day of the week, and then press ENTER:
=text(a1,"ddd")


If you want to return the day of the week as a whole word (for example, as Wednesday), type the following in the cell that you want to return the day of the week, and then press ENTER:
=text(a1,"dddd")

Method 3

In the cell that you want to return the day of the week, type the following, and then press Enter:
=weekday(a1,1)
Note The second variable indicates the day of the week that the week begins. For example, the variable "1" indicates that the week starts on Sunday, while the variable "2" indicates that the week starts on Monday.

Dates that are earlier than March 1, 1900, may have inaccurate results.

REFERENCES

For more information about how to use dates and times in Excel , click the following article number to view the article in the Microsoft Knowledge Base:
214094 HOW TO: Use Dates and Times in Excel

Properties

Article ID: 827327 - Last Review: January 18, 2007 - Revision: 2.2
APPLIES TO
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbexpertisebeginner kbdatetime kbfunctions kbinfo KB827327

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com