2

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.

Improve this question
1

2 Answers 2

Reset to default
0 

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

0

Improve this answer
0

  • 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)''")

    0

  • 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)''")

    0

  • 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)''")

enter image description here

  • 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)''")

Improve this answer
12
  • 2
    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
    Commented 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
    Commented 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
    Commented Mar 31, 2019 at 0:35
  • 2 Saturdays, 2 Thursdays and 1 Monday
    – rosa
    Commented 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
    Commented Mar 31, 2019 at 2:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.