# How do I turn a Google Sheets Duration Result from hours:minutes into just minutes?

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.

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:

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.

• Thanks to both you and Jack. I have to admit I'm still stuck. Please tell me there's an easy solution to this or something I'm overlooking... :-) I also can't figure out how to attach a graphic to a "Comment" so I'm going to submit an "answer" as well so I can show you.... Apr 26 '17 at 16:43

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

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

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:

Data after processing to correct format

• Your answer 1 works, but is needlessly complicated. A simple multiplication is enough to get the number of minutes without resorting to text manipulation. Your answer 2 is also good, but has the drawback that the resultant cell will still contain a value expressed as a fraction of the day, not the number of minutes. Apr 19 '17 at 8:33
• @ttarchala My second answer doesn't have the said drawback, it shows elapsed time in minutes and not as a fraction of the day. You literally select the format as elapsed minutes, "minutes" being the key word. My first answer is complicated, I agree but it is a generalized solution which can also be applied to other formatted text. Apr 19 '17 at 13:35
• The original poster clearly states, "I want to do calculations off that integer" - he needs this cell to contain the number of minutes, not just to show it. So I would insist that it is a drawback (in fact, not a fully correct answer). Apr 20 '17 at 15:06
• And he can make the said calculation from it, nothing is stopping him from doing so. The formula gives a number, not a text. This is because there is plus symbol in the formula itself, which forces it to a number! Apr 20 '17 at 15:14
• I was talking about your solution #2 (formatting as Elapsed minutes) - this keeps the cell value representing fraction of the day, not minutes. Apr 20 '17 at 15:19

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)
``````
• The given formula doesn't work correctly for all cases: it will give `270` for duration `148:30:00`, which isn't right. Nov 25 '19 at 8:06
• This also fails when durations are negative.
– jab
Feb 26 '20 at 22:46
• My complaint with the HOUR() function is that if you have a duration cell and the hour component is more than 24 hours, it will return the mod. ie. a cell with 25 hours is used with HOUR() it will return 1. Jun 29 '20 at 15:58

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

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.

Use the built-in `MINUTE` function:

``````=MINUTE(F2-E2)
``````
• Does not take into account the hours component of original data
– Nay
Jan 20 '19 at 4:53