Convert seconds to minutes / hours / time – Excel and Google Spreadsheets

This tutorial will demonstrate how to convert seconds into days, hours and minutes in Excel and Google Spreadsheets.

Converting seconds to minutes and seconds

First, divide the number of seconds by 86400 (the number of seconds in a day).

=$B3/86400

After doing this you will get the hour value as a number. To display the time value as a valid time (i.e. in minutes and seconds), format the cell with custom formatting.

1. Highlight the cells you want to format, for example: D3:D10.
2. On the ribbon, select Home > Number and click the small arrow to the right of the number group.

3. This will open the Format Cells dialog box.

4. Click on Custom at the bottom of the list and type mm:ss in the custom format text box.

5. Click OK.

Converting to hours, minutes, and seconds

Use the format hh:mm:ss to display in hours, minutes, and seconds.

Converting to days, hours, minutes and seconds in Excel

To display the days as well, you can create a custom formatting like the one below.

Converting seconds to minutes and seconds in Google Spreadsheets

As in Excel, the first step to convert elapsed seconds into time is to divide the value by 86400.

=$B3/86400

1. To format the cells to mm:ss, select Format > Number > More formats > More date and time formats from the Menu.

2. Delete the three formats shown by clicking each format and selecting Delete.

3. Scroll down the list and find a format with minutes and seconds.

4. Click on this format to add it to the bar at the top of the custom box.

5. Delete the Hour format from the custom format.
6. Click Apply to apply the formatting to the worksheet.

Converting to hours, minutes and seconds in Google Spreadsheets

In Google Spreadsheets, you use the same formula you use in Excel to convert seconds to hours by dividing the value by 86400.

=$B3/86400

You then use custom formatting to display the hours, as well as the minutes and seconds.

Converting to days, hours, minutes and seconds in Google Spreadsheets

In Google Spreadsheets, you use the same formula you use in Excel to convert seconds into hours, by dividing the value by 86400.

=$B3/86400

You then use custom formatting to display the days, as well as the hours, minutes, and seconds.

1. Delete the formatting in the Custom Date and Time Formats box, and then click on the drop-down box to the right of the custom format box to display all date and time options.

2. Select Day and enter “days”, Hour and enter “hours”, Minute and enter “minutes”, and Second and enter “seconds”

3. Click Apply.