How do I get the count number of weekdays in a datetime column in Google Sheets?

I have a timestamp in mm/dd/yy hh:mm:ss format from January to March. How do I count "how many days are Monday" in the range of A1:A199? (Or How Many are Tuesday, Wednesday, Saturday?

This gives me a value of 5

=ArrayFormula(COUNTUNIQUE(TEXT(A2:A199,"ddd")))


I need to know how many Mondays from Jan to Mar and by manually calculating there are 7 Mondays in my data (A1:A199) and 8 Thursdays and 9 Saturdays.

2 Answers

=ARRAYFORMULA(COUNTA(IFERROR(FILTER(A2:A, TEXT(A2:A, "ddd")="Mon"))))

• if you need all days:

=QUERY(ARRAYFORMULA(IF(LEN(A2:A), TEXT(A2:A, "dddd"),)),
"select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''")

• if you need all days from a certain time period:

=QUERY(ARRAYFORMULA(FILTER(TEXT(A2:A, "dddd"),
A2:A>=DATE(2019,4,1),
A2:A<=DATE(2019,4,22))),
"select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''")

• if you need all unique days per week:

=QUERY(ARRAYFORMULA(TEXT(SUBSTITUTE(TEXT(UNIQUE(IF(LEN(A2:A),
DATE(20&MID(A2:A, 7, 2), LEFT(A2:A, 2), MID(A2:A, 4, 2)), )),
"dd/mm/yyyy"), "30/12/1899",""), "dddd")),
"select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''")

• locale fix:

=QUERY(ARRAYFORMULA(TEXT(SUBSTITUTE(TEXT(UNIQUE(IF(LEN(A2:A);
DATE(20&MID(A2:A; 7; 2); LEFT(A2:A; 2); MID(A2:A; 4; 2)); )); "mm/dd/yyyy");
"12/30/1899";""); "dddd"));
"select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''")

• This did not work as expected. I need unique Monday count so for example, these 3 rows should count as one(1) Monday since the time is different but the day is same. 03/25/19 11:58:15 03/25/19 11:30:56 03/25/19 10:40:14 ... I know by manually reading and counting all 199 rows (and growing by 4 or 10 rows each unique day) that I currently have ... 7 Mondays in my data (A1:A199) and 8 Thursdays and 9 Saturdays.
– rosa
Mar 31, 2019 at 0:29
• 03/30/19 13:08:07 Saturday 03/30/19 12:59:13 Saturday 03/30/19 10:30:02 Saturday 03/30/19 10:28:28 Saturday 03/28/19 16:59:49 Thursday 03/28/19 13:07:02 Thursday 03/28/19 12:40:47 Thursday 03/28/19 11:09:00 Thursday 03/25/19 15:58:33 Monday 03/25/19 11:58:15 Monday 03/25/19 11:30:56 Monday 03/25/19 10:40:14 Monday 03/23/19 14:07:10 Saturday 03/23/19 12:56:01 Saturday 03/23/19 11:52:41 Saturday 03/23/19 11:34:13 Saturday 03/23/19 10:31:10 Saturday 03/21/19 14:23:38 Thursday
– rosa
Mar 31, 2019 at 0:34
• Don't know how to format that but it has 18 rows from 3/21 to 3/30
– rosa
Mar 31, 2019 at 0:35
• 2 Saturdays, 2 Thursdays and 1 Monday
– rosa
Mar 31, 2019 at 0:36
• Can't get it to work as it inserts bunch of rows with dates and then claims only 1 Saturday and 1 Sunday and I have 9 Saturdays and no Sundays. All the days show up at bottom of the list and for the first 20 or so rows, the dates don't match the data in A2:A199 ... will keep playing with it
– rosa
Mar 31, 2019 at 2:12