Convert Time to Minutes in Google Sheets: Easy Steps

Jason Gong
LAST UPDATED
June 6, 2024
TL;DR

Use simple formulas to convert time to minutes in Google Sheets.

By the way, we're Bardeen, we build a free AI Agent for doing repetitive tasks.

If you're working with time data, check out our GPT in Spreadsheets feature. It helps automate time conversions and other tasks to save you time.

Converting time to minutes in Google Sheets is a crucial skill for data analysis and reporting. In this step-by-step guide, we'll explore various techniques to efficiently transform time data into minutes, from basic formulas to advanced scripts. Whether you're working with standard time formats or complex durations, these methods will help you streamline your workflow and extract valuable insights from your time-based data.

Introduction

Google Sheets is a powerful tool for data analysis and manipulation, but working with time data can be tricky. Converting time to minutes is a common task that allows for easier calculations and comparisons. In this guide, we'll walk you through various methods to convert time to minutes in Google Sheets, including:

  • Understanding time formats
  • Using basic formulas
  • Handling complex durations
  • Utilizing custom cell formatting
  • Automating conversions with scripts

By the end of this guide, you'll be equipped with the knowledge and skills to connect Google Sheets for efficient data analysis, saving you time and effort in your projects. Let's get started!

Understanding Time Formats in Google Sheets

Google Sheets uses a default time format that represents durations as fractions of a day. This means that time values are stored as decimal numbers, with the whole number portion representing the number of days since December 30, 1899, and the fractional portion representing the time as a fraction of a day.

For example:

  • January 1, 1900 at noon is represented as 2.5 (2 days after December 30, 1899, and 0.5 because noon is half a day)
  • February 1, 1900 at 3 PM is represented as 33.625

When displaying time values, Google Sheets follows a specific format:

  • Hours are displayed in 24-hour format (0-23)
  • Minutes are displayed as two digits (00-59)
  • Seconds are displayed as two digits (00-59)

So, a time value of 0.75 would be displayed as 18:00:00, representing 6:00 PM. Understanding how Google Sheets handles time formats is crucial for accurately converting time to minutes and performing calculations with AI in spreadsheets.

Bardeen's GPT in Spreadsheets action helps you automate tasks like summarizing and analyzing data, saving you time and effort.

Basic Conversion Using Formulas

Google Sheets provides simple formulas to convert standard time formats into total minutes. To convert a time in cell A1 to minutes, use the following formula:

=HOUR(A1)*60+MINUTE(A1)

This formula extracts the hours and minutes from the time value in A1, multiplies the hours by 60 to convert them to minutes, and adds the remaining minutes.

To apply this conversion across multiple rows simultaneously, use the ARRAYFORMULA function in Excel:

=ARRAYFORMULA(HOUR(A1:A10)*60+MINUTE(A1:A10))

This will convert the time values in cells A1 through A10 to their corresponding total minutes.

For more advanced tasks, consider using AI web scraping tools to automate data extraction and integration.

Advanced Techniques: Handling Complex Durations

When working with durations that span over 24 hours or include seconds, you may need to use more advanced formulas in Google Sheets. Here are some techniques to handle these scenarios:

For durations that exceed 24 hours, use the following formula:

=INT(A1)*24+HOUR(A1)+(MINUTE(A1)/60)+(SECOND(A1)/3600)

This formula extracts the days, hours, minutes, and seconds from the duration in cell A1, converts them to their decimal equivalents, and adds them together.

If your duration includes seconds, modify the formula to include the SECOND function:

=HOUR(A1)+(MINUTE(A1)/60)+(SECOND(A1)/3600)

This formula converts hours, minutes, and seconds to their decimal equivalents and sums them up.

For cases where the time format is not recognized by Google Sheets, you can use the SPLIT function to separate the components and then perform the conversion:

=INDEX(SPLIT(A1, ":"), 0, 1)*3600+INDEX(SPLIT(A1, ":"), 0, 2)*60+INDEX(SPLIT(A1, ":"), 0, 3)

This formula splits the duration in cell A1 by the colon (":") and then multiplies each component (hours, minutes, seconds) by the appropriate factor to convert them to seconds before adding them together. You can also bring AI into your spreadsheet for more advanced automation.

Bardeen's playbook enrich LinkedIn profile links in Google Sheets makes it easy to update data, saving you time and effort.

Utilizing Custom Cell Formatting for Simplicity

Google Sheets offers built-in formatting options that allow you to directly display durations in minutes, making your data more readable and easier to understand. Here's how to change cell formats:

  1. Select the range of cells containing the time data you want to format.
  2. Click on the "Format" menu and choose "Number" from the dropdown.
  3. Scroll down to the "More formats" section and click on "More date and time formats."
  4. In the "Custom date and time format" field, enter the following format: [mm]
  5. Click "Apply" to apply the custom format to your selected cells.

By using the square brackets around "mm," Google Sheets will display the total number of minutes without any additional formatting. This is useful when you want to perform calculations or comparisons based on the minute values.

If you prefer to display the duration in a more human-readable format, you can use the format mm (without square brackets). This will display the minutes as "XX min" where "XX" represents the number of minutes.

Keep in mind that when using mm without square brackets, the cell value is treated as a time rather than a number, which may affect certain calculations or formulas.

By utilizing these custom cell formatting options, you can simplify the display of your time data and make it easier to analyze and interpret. For more advanced tasks, consider using GPT for Google Sheets to bring AI capabilities into your spreadsheets.

Automating Conversions with Google Sheets Scripts

Google Apps Script is a powerful tool that allows you to automate various tasks in Google Sheets, including time conversion. By creating custom scripts, you can streamline the process of converting time data into minutes across multiple cells or ranges.

To get started, open the Script Editor by clicking on "Tools" in the Google Sheets menu and selecting "Script editor." This will open a new window where you can write your script.

Here's a basic example of a script that converts a selected range of time data into minutes and writes the results to a new column:

function convertToMinutes() { var sheet = SpreadsheetApp.getActiveSheet(); var range = sheet.getActiveRange(); var values = range.getValues(); var outputValues = []; for (var i = 0; i < values.length; i++) { var time = values[i][0]; var minutes = (time.getHours() * 60) + time.getMinutes(); outputValues.push([minutes]); } var outputRange = range.offset(0, range.getNumColumns()); outputRange.setValues(outputValues);}

To use this script:

  1. Select the range containing the time data you want to convert.
  2. Run the "convertToMinutes" function from the Script Editor.
  3. The script will calculate the total minutes for each time value and write the results in a new column adjacent to the selected range.

You can further customize this script to handle different time formats, apply formatting to the output, or even create a custom menu item in Google Sheets to run the conversion with a single click.

By automating time conversions with Google Apps Script, you can save valuable time and reduce the risk of errors when working with large datasets or complex time calculations in Google Sheets. For more advanced automation, explore automating enrichment and qualification tasks with AI.

Save more time by using Bardeen's Google Docs integration. Automate your workflows in Google Sheets with a single click.

Automate Google Sheets Tasks with Bardeen

Converting time to minutes in Google Sheets can be done manually as described above, but imagine the possibilities if you could automate not just this, but many other repetitive Google Sheets tasks. Automation through Bardeen not only saves significant time but also minimizes errors, making your data management process more efficient.

Here are some powerful Google Sheets automations you can achieve with Bardeen:

  1. Get a daily summary of your emails and save to Google Sheets: This automation compiles a summary of your daily emails and saves it directly to a Google Sheet, perfect for tracking correspondence or managing customer support tickets efficiently.
  2. Copy a newly created Asana task to Google Sheets: Automate the process of documenting new tasks by copying details from Asana directly into your Google Sheets. Ideal for project managers and teams looking to streamline task tracking.
  3. Send a Slack message counting the rows in a Google Sheet: Keep your team informed by automatically sending Slack messages that count and report the number of rows in a specific Google Sheet. Great for sales teams tracking leads or finance teams monitoring expenses.

Discover more ways to enhance your productivity by downloading the Bardeen app at Bardeen.ai/download.

Contents
Automate Time Conversions with Bardeen

Bardeen's AI Agent automates time data conversions in Google Sheets effortlessly.

Get Bardeen free

Related frequently asked questions

Import PDF to Google Sheets: A Step-by-Step Guide

Learn how to import PDF into Google Sheets using Google Docs, CSV conversion, or third-party tools. Ideal for data analysis and complex document conversions.

Read more
Export ManyChat Subscribers to HubSpot: A Step-by-Step Guide

Learn how to export ManyChat subscribers and import them into HubSpot CRM for seamless data integration and efficient contact management.

Read more
Download Images & Charts from Google Sheets: A Guide

Learn how to download images and charts from Google Sheets directly or from a URL list to Google Drive, including step-by-step instructions.

Read more
Convert Salesforce Contacts to Leads: A Step-by-Step Guide

Learn how to convert contacts to leads in Salesforce using custom solutions or automation tools like Process Builder, including a step-by-step guide.

Read more
Excel Web Scraping Guide: Methods & Tips (3 Steps)

Learn how to web scrape with Excel using automated tools, Excel VBA, or Web Queries. Choose the best method for efficient data extraction and analysis.

Read more
Step-by-Step Guide to Adding Opportunities in Salesforce

Learn how to add, customize stages, and associate products with opportunities in Salesforce for effective sales pipeline management.

Read more
how does bardeen work?

Your proactive teammate — doing the busywork to save you time

Integrate your apps and websites

Use data and events in one app to automate another. Bardeen supports an increasing library of powerful integrations.

Perform tasks & actions

Bardeen completes tasks in apps and websites you use for work, so you don't have to - filling forms, sending messages, or even crafting detailed reports.

Combine it all to create workflows

Workflows are a series of actions triggered by you or a change in a connected app. They automate repetitive tasks you normally perform manually - saving you time.

get bardeen

Don't just connect your apps, automate them.

200,000+ users and counting use Bardeen to eliminate repetitive tasks

Effortless setup
AI powered workflows
Free to use
Reading time
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
By clicking “Accept”, you agree to the storing of cookies. View our Privacy Policy for more information.