Google Sheets – Using Alpha-Numeric Time Representation in a Formula

google sheets

I'm looking for a way to take raw data in the form of number of days, hours, and minutes (e.g. 1d 2h 3m) for one week and have the updated total subtracted from the prior week's entry to show the difference.

(e.g.: 7d 20h 44m – 7d 18h 12m = )

The issue I'm encountering is all the formulas I've tried so far work with numeric values only. The format of the data referenced is raw data pulled from a main source that updates regularly, so for the sake of speed and convenience would like to be able to maintain that format if possible.

Is there a way to do this in Google Sheets where the only variables pulled for the formula are the numbers, but still produce the same format with the results? #d #h #m

Best Answer

Suppose cell A2 has "7d 20h 44m" and cell B2 has "7d 18h 12m" . Then the following will compute the duration of the difference; be sure to format the output as duration for the displayed value to make sense. It will be 2:32:00.

=regexextract(A2, "(\d+)d") - regexextract(B2, "(\d+)d") + time(regexextract(A2, "(\d+)h"), regexextract(A2, "(\d+)m"), 0) - time(regexextract(B2, "(\d+)h"), regexextract(B2, "(\d+)m"), 0)

The first part, regexextract(A2, "(\d+)d") - regexextract(B2, "(\d+)d"), extracts the numbers of days and subtracts them. The second part handles hours and minutes by converting them to time like time(regexextract(A2, "(\d+)h"), regexextract(A2, "(\d+)m"), 0). The format is time(hours, minutes, seconds).

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 – Adding minutes to base time

Your question is if you have a time-value stored in a cell, and and integer value stored in another cell representing minutes, in a third cell (cell-three) how do I add the minutes represented by the integer in cell-two to the time-value in cell-one?

To do this you need to understand date value. Date values, even though they display for instance 9/26/2016, they are actually stored as an integer 42639 whose units are days, where day zero is 12/30/1899.

Time-values are also stored as days. This means that 10:30am is really 10.5÷24 days.

If you want to add 4 minutes to the time-value you'll have to make sure that it is also converted to day-units. So 4 minutes = 4÷24÷60 days.

If cell A4 is what you are calling "Base Time" & cell A5 is the cell you wish to add minutes to, then you can accomplish this by the formula =$A4+$B5/24/60