Excel handles dates and times numerically. The time’s serial number is between 0 and 1 (noon serial time = 0.50) in Microsoft Excel 2010.
To display the serial number behind time format in Microsoft Excel, we can use Format cells and Shortcut key.
The time format to display hours, minutes and seconds is HH:MM:SS. For example, a time of 14 hours and 56 minutes is displayed as 14:56:00
Problem is: -The default time format does not allow a time value to exceed 24 hours. For example, by entering a time of 28:56:00, the result is 04:56:00.
Solution is: -Change the format of the cell by placing brackets around the hour, [HH]:MM:SS. The result is displayed as 28:56:00.
Format Cells: -We will use this option to change time format.
We can find this option while right click with the mouse, pop-up will be visible then click on Format Cells.
Or we can use shortcut key Ctrl+~ on your keyboard.
Let’s take an example how we canreturn the total time values.
We have times in column A.
To change the format of cells, follow below given steps:-
- Select the cell A2:A6.
- Press the key Ctrl+1, and format cells dialog box will appear.
- Go to number tab, and select to Custom.
- Type [HH]:MM:SS, in the Type box.
- Click on OK.
This is the way by placing brackets around the hour [HH]:MM:SS we can perform totaling time values in Microsoft Excel 2010.