# Can Weekday value be shown in natural (Su, Mo, Tu..) format in Array Formula?

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")}
``````

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.
• 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

``````={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")}`````` • 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