How to Add the Day of the Week to a Date in Excel

excelA lot of folks seem surprised when they enter a date in Excel and it is not displayed in the manner it was keyed in. Unfortunately those same folks try to remedy this by keying in an apostrophe before the date. That will make the date display the way you keyed it in, BUT, yes, a big but there: it will also greatly diminish the myriad possibilities that properly formatted dates gives you in Excel.

When you use actual date values in Excel, you can avail yourself of many more options to display your dates exactly to your specifications, using Custom Number Formatting, such as displaying the weekday next to your date!

Follow the steps below to learn how:

  1. Key in your list of dates in cells A3:A15 (click CTRL + ; to enter the current date and then draft the Fill handle down to create your series of dates).
  2. Select range B3:B15 – key in =A3 and click CTRL + Enter to get a duplicate list of the dates you just created in Column A.
  3. With your dates in Column B still selected, click CTRL + 1 to open the Format Cells dialog box.
  4. On the Number tab, click the Custom category.
  5. In the type field, enter dddd .

excel custom numbers

  1. Click OK.

You now have a separate column showing the weekday for each date in column A.

day and date in excel


Posted in:
About the Author

Carol Bratt

Carol holds A+, MCP, and MOS computer certifications and is the resident DCT Office expert. She trains the staffs of small law firms in the use of Microsoft Office applications and has authored many books covering Microsoft Office as well as written articles for Infopackets, TechnoLawyer, and Digital Harbor. For more Microsoft Office tips visit Carol's Corner Office or follow Carol on FaceBook and Twitter.