# How to select weekdays only in a Google Sheets?

I have a table with two columns: column A has dates, column B has data. I want to compute the average of the data in B that corresponds to only week (work) days.

How can I write it as a formula?

You can use the WEEKDAY functionality to determine the day of the week, and then compute the Average of all days that are not Sunday (1) or Saturday (7).

``````WEEKDAY(number, type)
``````

Returns the day of the week for the given number (date value). The day is returned as an integer based on the type. Type determines the type of calculation: type = 1 (default), the weekdays are counted starting from Sunday (Monday = 2), type = 2, the weekdays are counted starting from Monday (Monday = 1), type = 3, the weekdays are counted starting from Monday (Monday = 0).

The you would need a collection of SumIF statements for each week day 2-6, and a count of them to determine an average.

• Can you please elaborate? Is it sth like this: =SUMIF(B1:B10, WEEKDAY(A1:A10)>2 & WEEKDAY(A1:A10)<6, 1) ? – EmreA Aug 1 '12 at 23:20
• It would probably be easier to add another column with the Weekday parameter, and then us that in your sumif and countif. I made a column C as =WEEKDAY(A1), Assuming the date is in Column A and the data in B: Your query would look like this: =(SUMIF(C1:C40,2,B1:B40)+SUMIF(C1:C40,3,B1:B40)+SUMIF(C1:C40,4,B1:B40)+SUMIF(C1:C40,5,B1:B40)+SUMIF(C1:C40,6,B1:B40))/(CountIF(C1:C40,2)+(CountIF(C1:C40,3)+(CountIF(C1:C40,4)+(CountIF(C1:C40,5)+(CountIF(C1:C40,6)))))) – bquaresma Aug 1 '12 at 23:45
• Sorry for how messy it is – bquaresma Aug 1 '12 at 23:47
• bquaresma: How do I modify the query so that I only take the work days within the last 1 month? – EmreA Aug 7 '12 at 3:04