CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Kelsey Jimerson
Kelsey Jimerson

Reputation: 73

In sheets is it possible to convert a duration formatted as a string into the number of minutes?

I am trying to convert a duration to a number. I have a cell that lists total hours and total minutes from a Google Meet. I am trying to automatically convert that information to the total number of minutes expressed as a number.

Ex: Cell 1(1 hr 8 min) to Cell 2 (68)

The google meet data, with the total hour/ total minutes) will be automatically pasted in from the meet report.

Is this possible?

Upvotes: 0

Views: 462

Answers (2)

Erik Tyler
Erik Tyler

Reputation: 9345

Supposing your original string were in A2, and that no meeting ran more than 9 hr 59 min, you could use this simple approach:

=TIME(LEFT(A2,1),MID(A2,6,2),0)*24*60

ADDENDUM (after more information added by poster)

This will cover your newly given scenarios:

=TIME(IFERROR(REGEXEXTRACT(A2,"(\d+) hr"),0),REGEXEXTRACT(A2,"(\d+) min"),0)*24*60

Upvotes: 0

Nabnub
Nabnub

Reputation: 1055

If you have the duration always expressed as X hr Y min,

UPDATED:

= ARRAYFORMULA(
    IFNA(
        IF(LEN(B2:B),
            REGEXEXTRACT(B2:B, "\d+") * 60 + REGEXEXTRACT(B2:B, "\D+(.+)min"),
            ""),
        REGEXEXTRACT(B2:B, "\d+")
    )
)

enter image description here

This part extracts the number before the hr and we multiply it by 60

REGEXEXTRACT(B2:B, "\d+")*60

This part extracts the number between hr and min and we add it to the above result:

REGEXEXTRACT(B2:B, "\D+(.+)min")

To adapt the formula to your situation, please consider the following notes:

  • Durations in the example starts from B2
  • Total is retrieved in C2:C
  • Arrayformula is used: i.e. only one formula is used in C2, and no need to copy the formula down

Upvotes: 3

Related Questions