1

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.

0

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

0

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

  • 1
    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 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 at 0:34
  • Don't know how to format that but it has 18 rows from 3/21 to 3/30 – rosa Mar 31 at 0:35
  • 2 Saturdays, 2 Thursdays and 1 Monday – rosa Mar 31 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 at 2:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.