Difficulty appending to cell in Google Sheets

Question

My goal is to automate making a calendar on Google Sheets. I used =DATE(C1,A1,B1) to make a date and then each following cell, I added 1 to the previous cell.

However, now I want to also automate making a time table with this spreadsheet. To append my list of times from below, I used '&', but then the date 12/16/2018 became 43450.

3-4
4-5
5-6
6-7
7-8
8-9
9-10

What am I overlooking? Any help is appreciated.

Answer 1

An aging, but good, question. You'll need a bit of understanding of how Sheets works "under the hood" to achieve this. There is no data type in Sheets for the "time range" type entries you listed out. You have a couple options from here

1. Embrace the supported data types: settle on something like times resembling 12/16/2018 3 PM instead of a range, with the understanding that 3 PM represents 3 to 4. Note that date-times in Sheets are just numbers, and you may set them to appear with whatever custom formatting you choose; 3 PM, 15:00:00, 20181216 3:00, etc.
   You would achieve this by adding (+) rather than concatenating (&) the time. Times are also stored in units of days(!), so with 3 PM being 15 hours into the day, it's stored as ¹⁵⁄₂₄ of a day; literally 0.625 days. Rest assured that Sheets will render it according to its defaults or your selected custom format. To grab the date in E1 and output 3 PM of that day, you use a formula resembling =E1+(15/24)
2. Fake it: achieve your cosmetic goal, since Sheets doesn't need to understand or further manipulate this time table. You would take the incoming date and force it to a plain-text string, to which you then append the time range string. Again with datetimes all stored in units of days, as you noticed, dates are just big numbers to make date math easier. So for your purposes, we'll need the TEXT function to turn it to plaintext before the concatenation, e.g. =TEXT(E1,"mm/dd/yyyy") & " 3-4"