Quite tricky to phrase this question but I’m asking getting times ranges of when people are available in 3 hours chunks. They can answer with any combination of "00:00 to 03:00", "03:00 to 06:00", "06:00 to 09:00", "09:00 to 12:00", "12:00 to 15:00", "15:00 to 18:00", "18:00 to 21:00", "21:00 to 00:00", and "All Day". In my current “solution” I replace these strings as numbers, 1 through 8 with “All Day” just being an overwrite and check if all the numbers are consecutive and if so, concatenate two pre-determined strings depending on what’s the first number and what’s the last numbers.
If, for instance, someone replied with the following answer: "06:00 to 09:00, 12:00 to 15:00, 15:00 to 18:00, 18:00 to 21:00", how do I properly shrink that to "06:00 to 09:00, 12:00 to 21:00"? In my solution the formula just fails since they’re not entirely consecutive. It will just return the original input string.
I think I probably need to address every answer as an array and somehow iterate through to see which values are consecutive, saving that to a separate array and then just replacing the text back to a readable format in another cell but I have no clue how I would go about comparing the first number to the second number without an obscene number of IF statements.
