- Excel Book Excerpt
Excel Enter Minutes and Seconds
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Enter Minutes and Seconds
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.
Figure 574 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.
Figure 575 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).
Figure 576 Two ways to correct time that has been entered incorrectly.
For more resources for Microsoft Excel: