# Convert cell containing days of the week in first-letter format to day names in Google Sheets

I created a schedule of events in Google Sheets.

• I have a cell that contains `MWF`, indicating that the event is every Monday, Wednesday, and Friday.
• I have a different cell that reports the word `Monday`.

I'd like to determine if my two events overlap.

In other words, I need to determine if `Monday` from the 2nd cell is included in `MWF` from the first cell.

• In this case, the result I want would report `TRUE`, while an alternative scenario in which the 2nd cell contained `Thursday` would instead report `FALSE` when comparing to `MWF`.

Visual example:

Is there a way I can do this without adding a separate region of a spreadsheet to define these letters?

• Ex: I don't want to add a column with `M, T, W, H, F` and another adjacent column containing "definitions" (i.e., `Monday, Tuesday, Wednesday, Thursday, Friday`) to refer back to.

## 1 Answer

you can try something like: `=ISNUMBER(SEARCH(LEFT(B1; 1); A1))`

# UPDATE:

above formula wasn't smart enough, but this one is:

``````=IF(LEFT(B1; 2)="Mo"; ISNUMBER(SEARCH(LEFT(B1; 1); A1));
IF(LEFT(B1; 2)="Tu"; ISNUMBER(SEARCH(LEFT(B1; 1); A1));
IF(LEFT(B1; 2)="We"; ISNUMBER(SEARCH(LEFT(B1; 1); A1));
IF(LEFT(B1; 2)="Th"; ISNUMBER(SEARCH(RIGHT(LEFT(B1; 2); 1); A1));
IF(LEFT(B1; 2)="Fr"; ISNUMBER(SEARCH(LEFT(B1; 1); A1));
IF(LEFT(B1; 2)="Sa"; ISNUMBER(SEARCH(LEFT(B1; 1); A1));
IF(LEFT(B1; 2)="Su"; ISNUMBER(SEARCH(RIGHT(LEFT(B1; 2); 1); A1)); )))))))``````

where:

``````Monday     =   M
Tuesday    =   T
Wednesday  =   W
Thursday   =   H
Friday     =   F
Saturday   =   S
Sunday     =   U``````

# UPDATE 2:

``````=ARRAYFORMULA(IF(LEN(A1:A)*LEN(B1:B), REGEXMATCH(TRANSPOSE(QUERY(TRANSPOSE({
REGEXMATCH(A1:A, REGEXEXTRACT(B1:B, "..")), REGEXMATCH(REGEXEXTRACT(B1:B, ".."),
REGEXREPLACE(IF(SPLIT(REGEXREPLACE(A1:A, "([A-Z])", " \$1"), " ")="", "♦",
SPLIT(REGEXREPLACE(A1:A, "([A-Z])", " \$1"), " ")), "..", "♦"))}),,999^99)), "T"), ))``````

• Thank you. This works for my example, but unfortunately won't work if I instead need to include both Tuesday and Thursday (or both Saturday and Sunday). Any thoughts about how I could approach this without relying on first letters (since some days share first letters)... – theforestecologist Sep 4 '18 at 18:45
• @theforestecologist thats the reason why I said "a lot of unknowns". how are you getting MWF and Monday? both by formula? or its just typed text? – user0 Sep 4 '18 at 18:49
• `MWF` and `Monday` would both be types as text. The unknown function would compare them. a second example that captures the issue of Tuesday/Thursday would, perhaps be, `TuTh` or `TH` instead of `MWF`... – theforestecologist Sep 4 '18 at 19:00
• thanks for adding the more detailed solution, user0! – theforestecologist Sep 4 '18 at 19:46