The data table contains daily closing prices spread across various months. Since we want to show the closing price for each weekday, we use Excel’s WEEKDAY() function to convert the date in column A into days of the week.
The serial number argument is the date you want to convert. For the first row, this is the date in cell A2. The formula is written as:
Cell C2 =WEEKDAY(A2,…)
The return type argument in this function lets you choose the convention of numbering days in a week by indicating what day the week starts. You will see a dropdown list of your options, which are:
- 1 – Numbers 1 (Sunday) through 7 (Saturday)
- 2 – Numbers 1 (Monday) through 7 (Sunday)
- 3 – Numbers 0 (Monday) through 7 (Sunday)
- 11 – Numbers 1 (Monday) through 7 (Sunday)
- 12 – Numbers 1 (Tuesday) through 7 (Monday)
- 13 – Numbers 1 (Wednesday) through 7 (Tuesday)
- 14 – Numbers 1 (Thursday) through 7 (Wednesday)
- 15 – Numbers 1 (Friday) through 7 (Thursday)
- 16 – Numbers 1 (Saturday) through 7 (Friday)
- 17 – Numbers 1 (Sunday) through 7 (Saturday)
By default, Excel’s week starts on a Sunday. If you want it to start on a Monday, for example, you should use 2 as the return type.
To see how this works, use the WEEKDAY() function for 2/9/2016 in cell A2 using a week that starts on a Monday:
Cell A2 =WEEKDAY(A2,2)
This results to a 2 since 2/9/2016 falls on a Tuesday. Changing the return type of the WEEKDAY() function to 1, gives you a weekday number of 1 instead of 2.
In this report, instead of the weekday numbers, we want to display the weekday names so that it displays as Sunday, Monday, and so on. To change the weekday number to a name, you can use Excel’s custom formatting. To do this:
- Left click on the cell containing the weekday number.
- Click on CTRL + 1 and that will display the Format Cells window. Alternatively, you can go to the HOME tab and click on the dropdown button in the Number section. Select More number formats and that should bring you to the Format Cells window as well.
- Under Category, select Custom.
- Under Type, use “dddd”. This will show your weekday as “Monday”, “Tuesday”, and so on. If you want to abbreviate the weekday to the first three letters such as “Mon”, “Tue”, etc, use “ddd” instead.
However, you will notice that instead of displaying “Tuesday” in cell C2, it gives you “Monday”. This is because Excel’s custom formatting uses the default week convention which starts with a Sunday and the number 2 is a Monday. To fix this, change your return type to 1 instead of 2 so that the week starts on Sunday:
Cell A2 = WEEKDAY(A2,1)
This now gives you the correct weekday name. Drag the formula down to the last row of your data table.
As an alternative, you can also use the TEXT() function which is written as:
Cell C2 = TEXT(A2,”dddd”)
This eliminates the step of selecting the return type and custom formatting like above and still gives you the same result.