Google-sheets – How to turn a Google Sheets Duration Result from hours:minutes into just minutes

google sheets

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.

Best Answer

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.

Formula

Hours
=SUM(A2:A5) + HOUR(TIME(0,SUM(B2:B5),0))

alternate way of notation
=HOUR(TIME(SUM(A2:A5),SUM(B2:B5),0))

Minutes
=SUM(B2:B5) - (HOUR(TIME(0,SUM(B2:B5),0)) * 60)

alternate way of notation
=MINUTE(TIME(SUM(A2:A5),SUM(B2:B5),0))

Explained

The hour calculation sums up the hour values and adds the sum of the minutes converted to hours. For the minutes, the calculation is a bit different. Here the sum of the minutes is subtracted by the hour count of those minutes, leaving only minutes ranging from 0 to 59.

In the second tab, the alternate formula is added.

Screenshot

Hours
enter image description here

Minutes
enter image description here

Note

Use a custom format (Format > Number > More Formats > Custom number format...) to obtain a leading zero, see screenshot:
enter image description here

Example

I've prepared an example file for you: How to Turn Leftover Minutes into Hours

Google-sheets – Google Sheets notification triggered from function result

Once you are running code in the Sheet, you can loop through the column to see if the values you care about are set or not. You can then only send the email if your criteria are met. Keep in mind that if your logic is if (empty > 20) you will get an email on entry 20, 21, 22, etc. You may want to send one every 5 entries over 20 or something like that.