1

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

1 Answer 1

1

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

4
  • 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)... Sep 4, 2018 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, 2018 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... Sep 4, 2018 at 19:00
  • 1
    thanks for adding the more detailed solution, user0! Sep 4, 2018 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.