There's a quiet frustration baked into this question, and it's one we know well: you just want your spreadsheet to show what you mean, not what the underlying engine thinks you mean. The person asking this isn't confused about time formats, they're fighting the gap between how we naturally think and how spreadsheet software stores data. That gap is real, and it's the reason so many people hit a wall when they try to make their data more readable. The good news? The solution isn't complicated, but it does require a shift in mindset: you have to stop treating the cell like a display box and start treating it like a container with rules.
Here's what's actually happening: when you type "9:00 AM," the spreadsheet isn't storing a time, it's storing a fraction of a day. That's why concatenating turns it into 0.375. The text you see is just a costume. So when you try to glue two cells together with "to" in between, the software dutifully grabs the underlying number, not the pretty face. And converting the cells to text? That only works if you do it before the formula runs, and even then, you're stuck with static values that don't update when your starting time changes. The real fix is to use the TEXT function inside your formula, something like `=TEXT(A1, "h:mm AM/PM") & " to " & TEXT(A1+0.020833, "h:mm AM/PM")`. That forces the spreadsheet to read the time as text for display purposes only, while keeping the underlying value intact for calculations.
What does this mean for you practically? It means you don't have to choose between a working formula and a readable label. You can have both, but only if you stop asking the spreadsheet to guess what you want and start being explicit about the format. The person who posted this is not alone; this is a classic rite of passage for anyone who's tried to build a schedule, a timesheet, or a project timeline. The spreadsheet isn't broken, and neither are you. You just need to learn the one function that bridges the gap between human-readable and machine-storable.
So here's the takeaway: don't abandon your formula because it spits out numbers. Don't settle for a column of static text that you'll have to manually update every time your start time changes. Learn the TEXT function, wrap it around your existing time references, and you'll get exactly what you wanted, a clean string that says "9:00 AM to 9:30 AM", without losing the ability to drag that formula down and have it auto-fill every half-hour slot. That's the whole trick. It's not about working harder; it's about giving the spreadsheet the instructions it actually needs.