12

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.

9

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.

  • 1
    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.... – MinnesotaDad Apr 26 '17 at 16:43
5

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

  • 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. – ttarchala 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. – Jack Brown 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). – ttarchala 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! – Jack Brown 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. – ttarchala Apr 20 '17 at 15:19
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.

  • It's customary to upvote/accept the answer if good :-) – ttarchala Apr 27 '17 at 21:27
-3

Use the built-in MINUTE function:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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