2

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.

EDIT: I ended up coding a custom formula for Google Sheets in the script editor as I felt that was more readable

2
  • Please share a link to a sample spreadsheet with, say, 5 entries of time listings placed in the same column they'll be placed in your real sheet. Also indicate which column should hold the condensed strings. Be sure to set the link's Share permission to "Anyone with the link... Editor." This will be the most efficient and effective way to receive help (or at least for me to be the one to provide that help).
    – Erik Tyler
    Jan 4 at 11:40
  • I've made an example sheet with columns marked as you said. I think my solution would probably to just use a custom formula written in JavaScript but if you can think of something native then I'd like to see what you come up with.
    – Ayal
    Jan 4 at 17:54
1

Given the examples in the post and in the sample spreadsheet (which are all in military time format of hh:mm), I used this in B1:

=ArrayFormula({"Condensed Answers";IF(A2:A="",,TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(ISNUMBER(SEARCH(A2:A,"All Day")),"All Day",A2:A)),"to 21:00, 21:00",""),"to 18:00, 18:00",""),"to 15:00, 15:00",""),"to 12:00, 12:00",""),"to 09:00, 09:00",""),"to 06:00, 06:00",""),"to 03:00, 03:00","")))})

First, the header is set (which can, of course, be changed).

From there, locate the central line:

TRIM(IF(ISNUMBER(SEARCH(A2:A, "All Day")), "All Day", A2:A))

Here, each element of A2:A is TRIMmed to be sure there are no superfluous spaces to start with. Then, if "All Day" is found anywhere, other times are omitted and just "All Day" is returned.

After that, it's just a series of SUBSTITUTEs working from the latest possible juxtaposed time to the earliest, replacing any existence of these with nothing.

Once all that is done, TRIM is used a final time to omit any resulting spacing that may have occurred from SUBSTITUTE calls.

4
  • That's perfect, does exactly what I need it to do, thank you
    – Ayal
    Jan 4 at 21:29
  • Great. Thanks for reporting back and marking as "Best Answer" so that other contributors know at a glance that the issue has been resolved.
    – Erik Tyler
    Jan 5 at 1:42
  • I actually found an edge case that your formula doesn't cover, 18:00 to 21:00, 21:00 to 00:00, All Day where someone by mistake added times even though they selected All Day as well. My script I wrote in JavaScript can detect and move around this but the formula stumbles on it. EDIT: Is it possible that in SEARCH the "All Day" and A2:A parameters need to be switched?
    – Ayal
    Jan 6 at 11:14
  • @Ayal, I've added another sheet ("Erik Help 2") with a modification to the formula.
    – Erik Tyler
    Jan 6 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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