CODEWITHSUNDEEP
exceldatetimetimeexcel-formula
vishnu prashanth
vishnu prashanth

Reputation: 419

Convert [h]:mm:ss to mm:ss in Excel

I have a data-set collected from GPS tracker. The total time in the data should be mm:ss. But Excel is interpreting it as hours and minutes.

How can I use a formula to convert it to Minutes and seconds? In the example below, 32 is supposed to be 32 minutes and 15 is supposed to be 15 seconds.

attached the screenshot of the dataset

Upvotes: 2

Views: 8230

Answers (2)

Vityata
Vityata

Reputation: 43585

An hour has 60 minutes. Thus, if you multiply the hour times 60, you would get the number of the minutes. Then you should add the seconds:

=HOUR(A2)*60 & ":" & SECOND(A2)

From the comment: The table displays the data correctly already. For record A2, the minutes in 32 and the seconds is 15.

If you think that your data is correct, then I have some bad news for you. Long story short - On C1, D1 and E1 write the following:

=Hour(A2)
=Minute(A2)
=Second(A2)

Then it will show:

8
15
0

Upvotes: 0

Greg Viers
Greg Viers

Reputation: 3523

Edit: It seems that the real issue is that the number before the colon is supposed to be minutes, but it has been stored as hours. So this is not just a display issue. You need to divide by 60 to turn hours to minutes and minutes to seconds.

The text function allows you to display numbers in a different format. You can also apply this format: [mm]:ss in the cell itself using cell formatting without using a formula.

If you want the total minutes:

=TEXT(A1/60,"[mm]:ss")

If you only want the minutes excluding hte hours:

=TEXT(A1/60,"mm:ss")

Upvotes: 2

Related Questions