CODEWITHSUNDEEP
google-sheetsformattingduration
mf94
mf94

Reputation: 479

Compute duration and express it in minutes and seconds

I have an Google sheet (on Google drive) with rowing data with 3 columns as so:

Duration(min)  Distance(m)  Split
20                    4127
20                    4143
23:23                 5000

I want to calculate the Split which is defined as the time you take to row 500m. The formula is: split = 500 * (time/distance)

On my sheet, for the first row, I therefore calculated:

split = 500*(20/4127) = 2.423067604

This result is correct, but I want it formatted in min:sec, so in this case the result is in fact 2 min and 25,2 seconds

I looked in the Format tab of Google sheets but seems to differ from the "standard" Excel software and I cannot find any option that makes me display 2:25,2 (or 2:25, if we round it).

Upvotes: 2

Views: 1302

Answers (1)

user6655984
user6655984

Reputation:

Sheets (and Excel) measure time in days. As pnuts said, you can get the correct units by dividing by the number of minutes in a day 

= 500*(20/4127) / 24 / 60

(24 hours in a day, 60 minutes in an hour).

After that, go to Format menu. Try the default "duration" format, but if you don't like it, go to More time and date formats at the end and put a format like

Elapsed minutes (1) : Seconds (01) : Milliseconds (1)

The difference between 1 and 01 is whether leading zero is needed. "Elapsed" means this field does not roll over into hours; it can be 75 minutes, etc. ("Elapsed" is what makes the difference between Time and Duration formats). "Milliseconds" are not exactly "milli", you get to choose to display 1/10 of seconds (as I did), or 1/100 of second, or 1/1000 of second.

Upvotes: 4

Related Questions