Enter Minutes and Seconds

July 12, 2022 - by Bill Jelen

Problem: I have to keep track of test data in minutes and seconds.

Strategy: This is much more difficult than anyone would think. Say that the first test took 1 minute and 30 seconds. You try entering 1:30 in a cell.

Enter 1:30 and Excel will think you mean 1 hour and 30 minutes. — Figure 567. What does the value in B2 really mean?

The result looks OK. However, when you click on the cell, you see that Excel has interpreted the entry as 1:30 AM, which is 1 hour and 30 minutes instead of 1 minute and 30 seconds.

The cell says 1:30, but the formula bar says 1:30:00 AM — Figure 568. Excel assumed you meant 1 hour and 30 minutes.

To enter minutes and seconds, you have to enter 0:01:30. This seems like a frustrating waste of extra keystrokes.

If you have entered a column of time in the wrong format, you can correct it with =TIME(0,HOUR(A2),MINUTE(A2)). Alternatively, multiply the times by (1/60).

Say you have entered a long column of times as 1:30 instead of 0:01:30. You can correct the time in B2 with =TIME(0,HOUR(B2),MINUTE(B2)). Or: you can use =B2*(1/60) — Figure 569. Two ways to correct time that has been entered incorrectly.

This article is an excerpt from Power Excel With MrExcel

Title photo by Alexandar Todov on Unsplash