Let's say you want to see the date displayed for a date value in a cell as "Monday" instead of the actual date of "October 3, 2005." There are several ways to show dates as days of the week.
Note: The screenshot in this article was taken in Excel 2016. If you have a different version your view might be slightly different, but unless otherwise noted, the functionality is the same.
Format cells to show dates as the day of the week
Select the cells that contain dates that you want to show as the days of the week.
On the Home tab, click the dropdown in the Number Format list box, click More Number Formats, and then click the Number tab.
Under Category, click Custom, and in the Type box, type dddd for the full name of the day of the week (Monday, Tuesday, and so on), or ddd for the abbreviated name of the day of the week (Mon, Tue, Wed, and so on).
Convert dates to the text for the day of the week
To do this task, use the TEXT function.
Copy the following data to a blank worksheet.