0

For Google sheet with the following data in A2:B4

06/29/2019 1:25 PM  1
06/30/2019 1:32 PM  1
07/01/2019 1:40 PM  1

The output will show

Monday   1  1
Saturday 1  1
Sunday   1  1

Can this be Saturday, Sunday, Monday etc?

={QUERY(ARRAYFORMULA(TEXT(SUBSTITUTE(TEXT(UNIQUE(IF(LEN(A2:A), DATE(20&MID(A2:A, 9, 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)''"), QUERY(QUERY({ARRAYFORMULA(IF(LEN(A2:A), TEXT(DATE(IF(LEN(A2:A),20,)&MID(A2:A,9,2),LEFT(A2:A,2),MID(A2:A,4,2)),"dddd"),)),B2:B}, "select Col1,sum(Col2) group by Col1 label sum(Col2)''"),"select Col2 where Col1 is not null")}
1

Yes, it can.

The formula in the question is very convoluted and there isn't a description of what it does, so here is a a simple formula that returns

Saturday
Sunday
Monday

Formula

=ArrayFormula(ARRAY_CONSTRAIN(TEXT(A2:A,"dddd"),3,1))

Explanation

  • TEXT(A2:A,"dddd") This returns the day of the week for all cells on A2:A
  • ARRAY_CONSTRAIN is used to limit the resulting array to a 3 rows by 1 column
  • ArrayFomula is used to tell to Google Sheets that is should display the whole array output.
| improve this answer | |
  • The formula shows output By Weekday Name, How many by that Weekday and Sum of Col B by that Weekday. – rosa Sep 23 '19 at 23:27
-1

={QUERY(ARRAYFORMULA(REGEXREPLACE(TEXT(SUBSTITUTE(TEXT(UNIQUE(IF(LEN(A2:A), 
  DATE(20&MID(A2:A, 9, 2), LEFT(A2:A, 2), MID(A2:A, 4, 2)), )), 
  "mm/dd/yyyy"), "12/30/1899",""), "ddd"), ".$", "")), 
  "select Col1,count(Col1) where Col1<>'' group by Col1 label count(Col1)''"), 
  QUERY(QUERY({ARRAYFORMULA(IF(LEN(A2:A), 
  TEXT(DATE(IF(LEN(A2:A), 20, )&MID(A2:A, 9, 2), LEFT(A2:A, 2), MID(A2:A, 4, 2)),
  "dddd"), )), B2:B}, 
  "select Col1,sum(Col2) group by Col1 label sum(Col2)''"),
  "select Col2 where Col1 is not Null")}

0

| improve this answer | |
  • Saturday 06/29 comes before Sunday 06/30 and Sunday is before Monday 07/01 so the order should naturally become Saturday Sunday Monday – rosa Apr 25 '19 at 19:51
  • The question was not about shortening the text of the weekday name but rather for the data to flow in the same order as the dates, ie Saturday then Sunday then Monday and also if there was a 07/02 and 07/03 then Sat, Sun, Mon, Tue, Wed order of display – rosa Apr 25 '19 at 20:00
  • I see, well that was not very understandable from the question – user0 Apr 25 '19 at 20:02
  • Sorry about that. This is why I added the last sentence asking "..Can this be Saturday, Sunday, Monday etc?" – rosa Apr 25 '19 at 20: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.