31

All I'm ultimately trying to do is to get a Duration result in Google Sheets to show up in a "total number of minutes" format vs in its default format of hours:minutes.

For example, if the Duration Result is displayed as "3:15" (meaning 3 hours and 15 minutes). I instead want it to be displayed as "195" (3 hrs x 60 minutes equals 180 plus 15, right?). I need it this way because I want to do calculations off that integer!

For example, if I want to dedicate 25% of my remaining time this afternoon to a task and I only have 195 minutes left, I want to have a formula that can multiply 25% of that remaining time number (in this example, 195) and return a result of 48.75 minutes.

0

6 Answers 6

37

The unit of measure for time representation in Google Spreadsheets is, by default, one day.

Therefore the time duration is in fact represented as a fraction of the day. To obtain the number of minutes, it's necessary to multiply it by 24 hours, then by 60 minutes/hour. Here is an example:

enter image description here

One thing though: when you make a formula like that, by default it will be also formatted as a time duration, and show 0. As the last step you must then format the cell as a number explicitly.

0
9

There are two ways of doing this:

  1. If you are willing to have the minutes displayed in a new cell you can do the following, the formula below assumes your duration is in cell A1

    =index(split(A1,":"),1)*60+index(split(A1,":"),2)
    

    The above will calculate the duration in minutes from A1 and input to the cell that as the above formula

  2. You can use the inbuilt formats to convert the time to elapsed minutes. Like so screen shot of number formatting options

    Then remove the preselected format setting and insert elapsed time in minutes format

    screen shot of custom date/time formats

Edit 1: This a example of how it looks when I process row 1 to the said format, and row 2 to get 25 percent of row 1

Input data: enter image description here

Data after processing to correct format enter image description here

0
3

For those who are reading in 2019 or later, I found a simple way to do it using HOUR and MINUTE functions.

Supposing that the duration is at cell A1, you can use:

=HOUR(A1)*60+MINUTE(A1)
2
  • 1
    The given formula doesn't work correctly for all cases: it will give 270 for duration 148:30:00, which isn't right.
    – AntonK
    Commented Nov 25, 2019 at 8:06
  • This also fails when durations are negative.
    – jab
    Commented Feb 26, 2020 at 22:46
0

There is indeed a simple fix to your problems.

One thing you must understand is the internal representation of date and time in the cell. In the column C you've entered the hour 17:30. It is represented internally as the number 0.73, because it's approximately that fraction of a day. In the column E, however, when you enter =NOW(), the part before the decimal dot is no longer 0 - it's today's date! and that is represented as the number of days since December 30, 1899. Same as in Excel, by the way. This is the reason why you see the huge negative number.

The simple fix is, instead of using just NOW(), put in the column E

=NOW()-TODAY()-(1/24)

Since TODAY() gives the current date without the fractional time, the subtraction of TODAY() from NOW() gives you just the fractional time. The column G will contain the correct number of minutes then.

0
0

(a cell or difference that has duration format)*24 and change cell format to number, G Sheet should handle it itself

-4

Use the built-in MINUTE function:

=MINUTE(F2-E2)
1
  • 1
    Does not take into account the hours component of original data
    – Nay
    Commented Jan 20, 2019 at 4:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.