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

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregex

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:

enter image description here

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.

Best 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"), ))

0

Related Topic