Google Sheets – Convert Weekday to Text Output

formulasgoogle sheets

In a monthly spreadsheet I have cells which contain the year (A1) Month (A2) and then the rest of the column running from cell A4 through to A34 numbered as the day in the Month (1 to 31).

My objective is to place the correct day of the week (in text) besides the day of the month (number).

So far I have successfully used the following formulae individually –

=(DATE(A1;A2;A5)) in this case returning 02/10/2020
=CHOOSE(A4;"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun") returning Mon (A4=1)
=WEEKDAY(DATE(A1;A2;A5);2) which gives 5 indicating Friday (correct for 02/10/20)
=DAY(DATE(A1;A2;A5))

WEEKDAY(date()) returns the number of the day and parameter 2 indicates that the week begins on Monday.

The date format required is the English version DD/MM/YY

What I now need to do is to somehow combine these formulae to copy down in cells B4 to B34 in order to place the correct day of the week against the day number in respect of that month but this so far eludes me.

A suggested working formula would be much appreciated.

The references I have used:

https://support.google.com/docs/answer/3092969?hl=en&ref_topic=3105385
https://support.google.com/docs/answer/3093371?hl=en
https://support.google.com/docs/answer/3092985?hl=en
https://support.google.com/docs/answer/3093040?hl=en

Interestingly, all these references suggest comma delimiters rather than semicolons between the formula elements.

Best Answer

There is no need to use a formula, this could be achieved by using cell format. Click Format > Number > More formats > Custom number formats then use dddd d

dddd for the day of the week.
d for the day of the month (1,2, etc. or dd for 01, 02, etc.)

Related Solutions

Google Sheets – Convert One Column of Multicolumn Output REGEXREPLACE from String to Number

Depending on the details of your use case, one of the following may be preferable:

Add a column with =ARRAYFORMULA(VALUE( B1:B100 )), assuming B1:B100 is the first output column. Then sort by this added column. This may be the easiest way to achieve the sort that you want.
Apply Value only to the first output. To my understanding, this requires splitting the regexreplace, so that first the replacement is made, then Value is applied, and only then the embedded array {...} is formed:

=ARRAYFORMULA({VALUE(REGEXREPLACE(A1:A100,...,"$1")), REGEXREPLACE(A1:A100,...,"$2")} )

If you choose this path, you might as well have two different formulas in two columns, one =ARRAYFORMULA(VALUE(REGEXREPLACE(A1:A100,...,"$1"))) and the other =ARRAYFORMULA(REGEXREPLACE(A1:A100,...,"$2")). I think this version is the most readable and flexible one.

Apply Value whenever it doesn't throw an error (regardless of columns). This could be done with IfError:

=ARRAYFORMULA(IFERROR(VALUE(REGEXREPLACE(A1:A100,...,{"$1", "$2"})),REGEXREPLACE(A1:A100,...,{"$1", "$2"})))

Google-sheets – Convert text automatically in Google Spreadsheets

You could just do regexreplace - something along the lines of this ( assuming your data starts just below the header in column B:

=ARRAYFORMULA(REGEXREPLACE(B2:B,"U of San Diego|University of San Diego","USD"))

You could also place your list of variations anywhere (pretend column A), and just join them with the pipe symbol like this which effectively creates your regex out of a list you can easily keep adding to:

=ARRAYFORMULA(REGEXREPLACE(B2:B,JOIN("|",A2:A),"USD"))

I forgot to mention - because your input is user generated - I suggest making your regex all lowercase, and referencing the data to be switched also in lowercase - thus avoiding any issues with case sensitivity, like this:

=ARRAYFORMULA(REGEXREPLACE(lower(B2:B),JOIN("|",indirect("A2:A"&counta(A:A))),"USD"))

Best Answer

Related Solutions

Google Sheets – Convert One Column of Multicolumn Output REGEXREPLACE from String to Number

Google-sheets – Convert text automatically in Google Spreadsheets

Related Topic