Return to Excel Formulas List

Convert Seconds to Minutes / Hours / Time – Excel & Google Sheets

Download Example Workbook

Download the example workbook

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

convert seconds to minutes hours Main Function

Convert Seconds to Minutes and Seconds in Excel

The first step to converting elapsed second to time is to divide the value by 86400.  The reason for this is that there are 86400 second in a day ie: in 24 hours.

MinutesSeconds

Once you have done this, you will get the time value as a number. In order to display the time value as a valid time ie in minutes and seconds, you need to format the cell with custom formatting.

  1. Highlight the cells you wish to format eg: D3:D10.
  2. In the Ribbon, select Home > Number and then click on the little arrow on the right-hand side of the number group.

Number Format

  1. The Format Cells dialog box will be shown.

Number Format 2

  1. Click on Custom at the bottom of the list, and then type mm:ss into the custom format text box.

Number Format 3

  1. Click OK.

 

Convert to Hours, Minutes and Seconds in Excel

You use the same formula to convert the seconds to time by dividing the value by 86400.

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

HoursMinutesSeconds

 

Convert to Days, Hours, Minutes and Seconds in Excel

You use the same formula to convert the seconds to time by dividing the value by 86400.

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

DaysHoursMinutesSeconds

 

Convert Seconds to Minutes and Seconds in Google Sheets

As with Excel, the first step to converting elapsed second to time is to divide the value by 86400.

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

Google 01

Google 02

  1. Delete the 3 formats shown by clicking on each format and selecting Delete.

Google 03

 

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

Google 04

 

  1. Click on that format to add it to the bar at the top of the custom box.

Google 5

 

 

  1. Delete the Hour format from the custom format.Google 06
  2. Click Apply to apply the formatting to your worksheet.Google 07

 

Convert to Hours, Minutes and Seconds in Google Sheets

You use the same formula in Google Sheets as you do in Excel to convert the seconds to time by dividing the value by 86400.

Google 08

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

Google 09

 

Convert to Days, Hours, Minutes and Seconds in Google Sheets

You use the same formula in Google Sheets as you do in Excel to convert the seconds to time by dividing the value by 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 the date and time options.

Google 10

  1. Select Day and then type “days’, select Hour and then type “hours”, select Minute and then type “minutes” and then select Second and type “seconds”

Google 11

  1. Click Apply.

Google 12