2

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

1

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).

  • That just about works, but do you have any suggestions for formatting the regex output to read like the example you gave (2:32:00) without the AM/PM time suffix? – UNST3RBLICH Oct 21 '17 at 22:09
  • I tried changing 'time' to 'duration' but does not seem to work. – UNST3RBLICH Oct 21 '17 at 22:36
  • Ok, got it working after tinkering a bit! Thank you greatly for the regex formatting help! – UNST3RBLICH Oct 21 '17 at 22:40

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.