Most valuable professional
  • 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.

LE10000590.jpg 

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.

LE10000591.jpg 

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).

LE10000593.jpg 

Figure 576 Two ways to correct time that has been entered incorrectly.

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: