Convert Decimals to Minutes and Minutes to Decimals in Google Sheets

Published on

Learning how to convert decimals to minutes and minutes to decimals in Google Sheets is essential for various calculations. This skill is particularly useful in preparing payrolls, final settlements for employees, calculating rental bills, and more.

Introduction

You may have employee work hours recorded in HH:MM format (as duration). However, this format is not suitable for payroll calculations because it does not directly allow multiplication with hourly wages.

For example, suppose an employee has worked 10:30 (10 hours and 30 minutes) at a rate of $35.00 per hour. If you try to calculate their pay as:

=TIMEVALUE("10:30")*35

or

=10.30*35

Neither formula will return the correct answer.

Note: You can enter the value in A1 and use the formula =A1*35 instead.

The correct calculation should use 10.50 hours instead of 10:30 because 30 minutes is 0.5 in decimal hours.

Similarly, you may sometimes need the reverse conversion—for instance, converting 10.50 decimal hours back to 10:30.

In this tutorial, you will learn how to convert decimals to minutes and minutes to decimals in Google Sheets using simple formulas.

1. Convert Time or Duration (hh:mm) to Hours

In the following example, column B contains the start time (B2:B11), and column C contains the end time (C2:C11).

To calculate the duration, use this formula in D2 and drag it down to D11:

=C2-B2
Time Duration Between Start and End Time

To display the correct total, format D2:D11 as Duration: Format > Number > Duration

This prevents truncation if the total duration exceeds 24 hours.

Formula to Convert Time to Hours (Whole Number or Decimal)

=N(time_or_duration * 24)

Applying this formula in E2 (and dragging it down) will give the duration in hours (which may be a whole number or a decimal):

=N(D2*24)
Convert Time or Duration (hh:mm) to Hours – Example

2. Convert Time Duration (hh:mm) to Minutes

For shorter durations, you may want to convert time to minutes instead of hours.

Example:

  • If a journey from Location A to B takes 0:35:00 (35 minutes),
  • Instead of displaying 0.58 hours, you can directly convert it to 35 minutes.

Formula to Convert Time to Minutes

=N(time_or_duration*1440)

For example, if D2:D3 contains duration, use this formula in E2 and drag it down:

=N(D2*1440)
Convert Time Duration (hh:mm) to Minutes – Example

3. Convert Hours to Time (hh:mm)

If column A2:A11 contains hours (whole number or decimal), you can convert them back to time format using this formula in B2:

=A2/24

Then, drag it down to B11.

Convert Hours (Whole Number or Decimal) to Time (hh:mm) – Example

Formatting Tip

  • Format B2:B11 as Time or Duration, depending on your needs.
  • Use Duration if you plan to sum the values to prevent resetting after 24 hours.

4. Convert Minutes to Time (hh:mm)

If A2:A4 contains minutes, convert them to time using:

=A2/1440

Format B2:B4 as Time or Duration.

Convert Minutes to Time (hh:mm) – Example

5. Convert Hours to Minutes

To convert hours to minutes, multiply by 60.

Example:

  • An employee worked 1.5 hours.
  • To convert it to minutes:
=A1*60 // returns 90 minutes

If A1 contains 24 (hours), the result will be 1440 minutes (1 full day).

6. Convert Minutes to Hours

To convert minutes to hours, divide by 60.

Example:

If A1 contains 90 minutes, the following formula returns 1.5 hours:

=A1/60

For A1 = 1440 (1 full day in minutes), the result will be 24 hours.

7. Convert Decimal Minutes to Minutes and Seconds

If you have decimal minutes (e.g., 5.5 minutes), split it into minutes and seconds:

=INT(A1)  // Returns the minutes
=ROUND((A1 - INT(A1)) * 60, 0)  // Returns the seconds

For A1 = 5.5, this will return 5 minutes 30 seconds.

8. Convert Minutes and Seconds to Decimal Minutes

If:

  • A2 contains minutes
  • B2 contains seconds

You can convert them to decimal minutes using:

=A2+(B2/60)

Example:

ABC
MinutesSecondsDecimal Minutes
5305.5
2452.75

Resources

Here are related tutorials you may find useful:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

2 COMMENTS

  1. It confuses me. I need to convert 26.64 hours, and the result should be 26:38.

    However, divide 26.64 by 1440, and you get 0.02.

    Convert it to time format. You get 00:26 (26 minutes).

    • Hi, Ian,

      I assume you have 26.64 in cell C12. Then you can try the following formula.

      =trunc(int(C12)+mod(C12,1)*60/100,2)

      But it is equally important how you have arrived at that value, i.e., 26.64.

      If you got it by adding numbers, consider the following formula, which will return 27:04 when formatted to duration.

      =value(SUBSTITUTE(C12,".",":"))

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.