Excel calculates time in a specific calendar format, in order to convert days, hours, minutes and seconds. While cells in a spreadsheet can be formatted to reflect time and formulas will adjust to reflect the time format, data must be entered in hours:minutes:seconds (hh:mm:ss) for accurate calculations. Decimal values are converted according to the default start date for the application (e.g., 1/1/1904 12:00:00 AM). Be sure to format the cells before entering data and once time data and formulas are entered, descriptive charts and graphs can be generated to show time.
- Skill level:
Other People Are Reading
Select and highlight the group of cells in your spreadsheet (such as a column or a row) where data will be entered as time in minutes and seconds.
Select "Cells" under "Format" in the top menu bar.
Click the "Number" tab and select the "Custom" option from the list of categories on the left.
Select "h:mm:ss" from the list of custom format or type "h:mm:ss" in the field under "Type."
Click the "OK" button.
Select the cell that will be used to calculate the total number of minutes and seconds entered.
Type the following part of the formula, "=sum(" then select the first cell containing the data.
Type "+" and then select the next cell containing the data. Repeat this step to select the cells to be calculated.
Type ")" to complete the formula and press the "Enter" key.
Tips and warnings
- If only minutes and seconds are tracked, enter the data as "00:mm:ss" where "mm" are minutes and "ss" are seconds because Excel converts time data to a calendar entry.
- Consider displaying the time data on a chart, such as a bar chart or a pie chart to show time tracked: Select "Chart" under "Insert" and choose the type of chart. Click the "Preview" button to see how your data will appear. Continue through the chart wizard to type labels and choose options before clicking the "Finish" button.
- Data previously entered in decimal format will convert to a date/time calculation. These values must be revised to the hh:mm:ss format.
- Depending on your version of Excel, formatting cells in the custom type of "mm:ss" may yield inconsistent time conversions. Specifying "hh:mm:ss" yields consistent results across versions.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for