Google-sheets – How to get the count number of weekdays in a datetime column in Google Sheets

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-querygoogle-sheets-timestamp

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.

Best Answer

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

Related Solutions

Google Sheets – How to Get Unique Number of Days in Datetime Column

Assuming your dates are in A1:A30, the following array formula worked for me:

=ArrayFormula(COUNTUNIQUE(TEXT(A1:A30,"yyyymmdd")))

Google-sheets – Count the number of words in a string in Google Sheets

In Google Spreadsheets, I would do it a bit different.

Formula

=COUNTA(SPLIT(A1, " "))

Explained

The SPLIT function is only available in Google Spreadsheet and will split the cell's content on every space (" "). The COUNT function will simply count the instances.

The Excel formula gives the same answer, but a bit more laborious:

=LEN(TRIM(A1))-LEN(SUBSTITUTE(A1," ",""))+1

Screenshot

enter image description here

Remark

See following answer on which formulae are unique to Google Spreadsheet:
https://webapps.stackexchange.com/a/44719/29140