2

I am converting [ss] seconds to mm:ss format.
But, I also have to round off the value to the nearest minute.
For example, 19:29 -> 19 minutes and 19:32-> 20 minutes

I have tried using mround function. But it did not work. 

=MROUND(19.45,15/60/24) gives output as 19.44791667.

It should come as 20 seconds.

player0
  • 124,011
  • 12
  • 67
  • 124
Hadan
  • 91
  • 1
  • 10

1 Answers1

0

try like this where B column is formatted as Time

=ARRAYFORMULA(IF(LEN(A1:A), MROUND(A1:A, "00:01:00"), ))

0

=TEXT(MROUND("00:"&TO_TEXT(B5), "00:01:00"), "mm:ss")

0

=ARRAYFORMULA(TEXT(MROUND(SUM(TIME(0, 
 REGEXEXTRACT(TO_TEXT(C3:C11), "(.+):"),
 REGEXEXTRACT(TO_TEXT(C3:C11), ":(.+)"))), "00:01:00"), "[mm]:ss"))

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • I am sorry. it is not working for me. Actually, my data is like this https://imgur.com/a/Hy7cMDK. I just want the sum of the total time rounded off to the nearest minute. What would be the best way to achieve this ? – Hadan Aug 06 '19 at 16:10
  • Thanks for your reply. I still am not able to get this work.May be its related to the formatting of time. Here is the link to my sheet. https://docs.google.com/spreadsheets/d/1oA9SdNm5lC8aCgijzpetAHSi-LWG-l6kIyYoVCmz_bY/edit#gid=0 – Hadan Aug 06 '19 at 17:56