In this example, there are hourly air measurements of nitric oxide in January 2015 on the side of a road. I want to find out if Saturdays and Sundays have lower nitric oxide concentrations because there’s less traffic on those days.
This formula will get the first three letters of the weekday from the date. The “d” stands for day.
=TEXT(A2,”ddd”)
Using four d’s in the formula will get the weekday as a whole word.
=TEXT(A2,”dddd”)
The WEEKDAY function will give you the weekday as a number.
=WEEKDAY(A2)
The WEEKDAY function defaults to Sunday being the number 1, but you can change this.
To change the number formatting of cells, select the cells and then use the keyboard shortcut Ctrl + 1 to open the Format Cells box. To change the formatting to show the day of the week, go to Custom and type in “dddd”. This will change the number from the WEEKDAY formula to show the day of the week. However, if you use something other than the default numbering in the WEEKDAY formula, then it will show the wrong day. For example, 4 will always be Thursday in the custom formatting, no matter what it is in the WEEKDAY formula.
It is also possible to change the formatting of cells with dates in them, to show the weekday.
You show the weekdays on a chart, go to the Insert Tab and insert a scatter chart.
Then select the axis and change the number formatting on the axis to a Custom Format, and click Add. In this example, dddd shows the weekday, and dd shows the day, i.e. 01 is the 1st day of the month & 15 is the 15th day of the month.
In order to use weekdays in a Pivot Table, make a column showing the weekday, select your data, go to the Insert Tab and insert a Pivot Table.
Put the Weekday field into the Axis (Categories) box, and in the Values box, put the Average of the Nitric Oxide concentrations. We can now see that on average concentrations are lower at the weekend, most likely because there is less traffic.
Its also possible to select all the weekdays in the pivot table and group them together.
Then group the weekend days together as well.
Change the names of both of these groups to Weekdays and Weekends.
Then, collapse both of these groups to get the average of the weekdays and weekends, and more easily see the difference between the two of these on the chart.
