Using alpha-numeric time representation (days, hours, minutes) in a formula - Web Applications Stack Exchange most recent 30 from webapps.stackexchange.com 2019-08-20T11:43:20Z https://webapps.stackexchange.com/feeds/question/111020 http://www.creativecommons.org/licenses/by-sa/3.0/rdf https://webapps.stackexchange.com/q/111020 2 Using alpha-numeric time representation (days, hours, minutes) in a formula UNST3RBLICH https://webapps.stackexchange.com/users/169252 2017-10-21T20:52:32Z 2017-10-21T21:47:57Z <p>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.</p> <p>(e.g.: 7d 20h 44m - 7d 18h 12m = )</p> <p>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.</p> <p>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</p> https://webapps.stackexchange.com/questions/111020/-/111023#111023 1 Answer by user135384 for Using alpha-numeric time representation (days, hours, minutes) in a formula user135384 https://webapps.stackexchange.com/users/0 2017-10-21T21:46:31Z 2017-10-21T21:46:31Z <p>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.</p> <pre><code>=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) </code></pre> <p>The first part, <code>regexextract(A2, "(\d+)d") - regexextract(B2, "(\d+)d")</code>, extracts the numbers of days and subtracts them. The second part handles hours and minutes by converting them to time like <code>time(regexextract(A2, "(\d+)h"), regexextract(A2, "(\d+)m"), 0)</code>. The format is time(hours, minutes, seconds). </p>