Managing time in spreadsheets can sometimes be a frustrating endeavor. We’ve all been there, struggling with time formats, dealing with unexpected errors, or trying to perform calculations that just don’t seem to work. Yet, as teachers, we frequently need to harness time for scheduling classes, setting assignment deadlines, planning events, and more. Time management is essential in education, and having the right tools can make all the difference.
Adding Time to Google Sheets
Try my Google Sheets spreadsheet template. Notice on the first sheet I have started a time of 8:15am. Type an end time in the next spreadsheet of 5 minutes later. (8:20 AM). Not too difficult, but you do not want to do this 5 minutes increment thing manually. The point of using a spreadsheet is to have it do these types of calculations. How would you create a list of start and end times that are 5 minutes apart?
Using =TIME() in Google Sheets
The formula =TIME(Hour, Minute, Second) sets the time with the indicated hour, minute, and second. =TIME(10,3,20) registers a time of 10:03:20 AM. While this may seem sillier than just typing the time manually, using =TIME() in combination with cell referencing would be helpful.
Set a cell with the number of minutes you want to increment. In my example I chose cell D1 to be 5 minutes. In the spreadsheet I added the previous time to the incremented time to construct the new end time. If I change the value in D1, the values in all the referenced cells will also change.
Displaying in One Cell Start Time - End Time
Concatenate cell values with the ampersand symbol.
=A3&” – “&B3 should display the start and end time together… but it goes wrong.
You’ve meticulously inputted start and end times in your Google Sheets, eager to display them together in a clear, readable format. But as you try to combine them using the trusty ampersand (&), you’re met with a frustrating jumble of decimals instead of the neatly formatted times you envisioned. This common hiccup can leave even the most tech-savvy teacher scratching their head. Fear not, for there’s a simple solution to this formatting conundrum.
=Time() Displays are a False Friend
The culprit behind this issue lies in how Google Sheets handles time values internally. When you use the & operator to concatenate cells containing times, Sheets converts those times into their underlying numerical representation – a decimal value between 0 and 1, where 0 represents midnight and 1 represents 11:59:59 PM. To regain control over the display format and present those times in a human-readable way, we need to enlist the help of the TEXT function.
=TEXT() is Your Friend
The TEXT function in Google Sheets is a formatting powerhouse, allowing you to transform numerical values, including time values, into text strings with your desired format.
=TEXT(value, format)
TemplateTab by AliceKeeler
Duplicate a template/graphic organizer for each student on the roster in Google Sheets™. Ability to export tabs to individual sheets.
Collects NO user data.
value: The cell or value you want to format.
format: A text string specifying the desired format. For time values, you can use common time format codes such as:
Formatting Options
hh:mm: Displays hours and minutes in 12-hour format (e.g., 3:15 PM)
hh:mm:ss: Displays hours, minutes, and seconds in 12-hour format (e.g., 3:15:22 PM)
HH:mm: Displays hours and minutes in 24-hour format (e.g., 15:15)
HH:mm:ss: Displays hours, minutes, and seconds in 24-hour format (e.g., 15:15:22)
AM/PM: Displays AM or PM after the time
Concatenate with =TIME() and =TEXT()
Either by using the =TIME() in the cell referenced or within the formula, combine with =TEXT() to get the desired formatting outcome.
=TEXT(A1, “hh:mm AM/PM”) & ” – ” & TEXT(B1, “hh:mm AM/PM”)
This sets the start time in the cell, formatted for 12 hour time, with a dash before the end time.
Mastering =TIME()
Mastering time formatting in Google Sheets is a valuable skill for any teacher seeking to optimize their workflow and create visually appealing, informative spreadsheets. Whether you’re managing class schedules, tracking student progress, or analyzing data, the ability to control how time values are displayed empowers you to communicate information effectively and make data-driven decisions.
