There is a better way to build a shift timeline, and it does not require a single formula. The user who posted this request already knows what they want: paste raw shift data into column A and watch the sheet fill itself in, hour by hour. They are right to be frustrated with the "incorrect AI slop" they found. Most search results serve generic conditional formatting tutorials that assume your data is already clean, labeled, and sitting in neat columns. Real work is never that tidy.
What this user needs is a conditional formatting rule that reads the text string "08:00-12:00" and paints the corresponding columns. It is entirely possible, and the solution is more elegant than most people expect. The trick is to avoid splitting the text into separate start and end cells. Instead, you write a single rule that extracts the two times using the VALUE and LEFT/RIGHT functions, then checks whether the column header falls between them. The formula looks something like this: =AND(E$1>=VALUE(LEFT($A2,5)), E$1<=VALUE(RIGHT($A2,5))). Apply that to the entire range of time columns, and the formatting will update automatically every time you paste a new shift into column A.
This approach matters because it respects how people actually work. Nobody wants to reformat their data to fit the tool. The tool should adapt to the data. When you can paste a raw schedule and see a visual timeline appear instantly, you stop fighting the spreadsheet and start using it. The user who posted this question is not asking for a clever workaround. They are asking for a tool that understands time the way they do: as a continuous block, not a pair of disconnected numbers. That is the standard we should hold spreadsheets to.
So here is the practical takeaway. Open your sheet, set up your column headers from 08:00 to 20:00, and write that conditional formatting rule. Test it with one shift, then paste the rest. If the formatting does not behave, check that your column headers are stored as actual time values and not text. Once it works, you will have a live visual schedule that updates with every paste. No macros, no manual cell-by-cell coloring, no third-party add-ins. Just a sheet that does what you meant it to do.