ExcelFunctions.net

Search Site:

Related Page:

Seconds To Time In ExcelThe easiest way to convert a time to a decimal (i.e. to a number of hours, minutes or seconds) in Excel is to multiply by the number of hours, seconds or minutes in a day.

I.e.

- To convert a time to a number of
**hours**, multiply by**24**(the number of hours in a day); - To convert a time to a number of
**minutes**, multiply by**24*60**(the number of minutes in a day); - To convert a time to a number of
**seconds**, multiply by**24*60*60**(the number of seconds in a day).

Note: If the result of your formula is not displayed as a decimal, you need to change the cell formatting so that it displays a decimal. The easiest way to do this is to select the cell containing the formula and then select the 'General' format type from the **Home** tab of the Excel ribbon.

'General' format type on the Excel ribbon

For further details, with examples, see the following sections:

The following spreadsheets show two different formulas that can be used to convert a time to a number of hours in Excel.

The simplest formula to convert a time to a number of hours is shown below. In this case, the time is simply multiplied by 24 (the number of hours in one day).

A | B | |
---|---|---|

1 | Time (hh:mm:ss) | Hours (decimal) |

2 | 02:30:00 | =A2 * 24 |

The formula in cell B2 of the above spreadsheet returns the value *2.5*.

I.e. 2 hours 30 minutes and 0 seconds is equal to *2.5* hours.

*(The reason that this method works is because Excel times are internally stored as decimal values, with the value 1.0 used to represent 24 hours, and therefore, the value 1/24 used to represent 1 hour).*

The second formula that can be used to provide the same result uses the Excel Hour, Minute, and Second functions. Although this formula is much longer than the simple multiplication shown above, some people prefer it.

A | B | |
---|---|---|

1 | Time (hh:mm:ss) | Hours (decimal) |

2 | 02:30:00 | =HOUR(A2) + MINUTE(A2) / 60 + SECOND(A2) / 3600 |

Again, the formula in cell B2 of the above spreadsheet returns the value *2.5*.

Cell B2 of the spreadsheets below show the two formulas that can be used to convert a time to a number of minutes in Excel.

The simplest way to convert a time to a number of minutes is to multiply the time by 1440, which is equal to 24*60 (the number of minutes in one day):

A | B | |
---|---|---|

1 | Time (hh:mm:ss) | Minutes (decimal) |

2 | 02:30:30 | =A2 * 1440 |

For the time 02:30:30, the formula in cell B2 of the above spreadsheet returns the value *150.5*.

I.e. 2 hours 30 minutes and 30 seconds is equal to *150.5* minutes.

*(The reason that this method works is because Excel times are internally stored as decimal values, with the value 1.0 used to represent 24 hours, and therefore, the value 1/1440 used to represent 1 minute).*

The same result can also be obtained using the Excel Hour, Minute and Second functions, as shown in the spreadsheet below:

A | B | |
---|---|---|

1 | Time (hh:mm:ss) | Minutes (decimal) |

2 | 02:30:30 | =HOUR(A2) * 60 + MINUTE(A2) + SECOND(A2) / 60 |

Again, the formula in cell B2 of the above spreadsheet returns the value *150.5*.

The spreadsheets below show the formulas that can be used to convert a time to a number of seconds in Excel.

The easiest way to convert a time to a number of seconds is to simply multiply the time by 86400, which is equal to 24*60*60 (the number of seconds in one day):

A | B | |
---|---|---|

1 | Time (hh:mm:ss) | Seconds (decimal) |

2 | 02:30:30 | =A2 * 86400 |

For the time 02:30:30, the formula in B2 of the above spreadsheet returns the value *9030*.

I.e. 2 hours 30 minutes and 30 seconds is equal to *9030* seconds.

*(The reason that this method works is because Excel times are internally stored as decimal values, with the value 1.0 used to represent 24 hours, and therefore, the value 1/86400 used to represent 1 second).*

The same result can be obtained using the Excel Hour, Minute and Second functions, as shown below:

A | B | |
---|---|---|

1 | Time (hh:mm:ss) | Seconds (decimal) |

2 | 02:30:30 | =HOUR(A2) * 3600 + MINUTE(A2) * 60 + SECOND(A2) |

Again, the formula in B2 of the above spreadsheet returns the value *9030*.

When you convert a time to a decimal, the cell containing the result may have the wrong formatting (e.g. the result may be displayed as a time, instead of a decimal).

In this case, you will need to format the cell to display a decimal. To do this you can use either of the following two methods:

The easiest way to change a cell's formatting is to select the cell(s) to be formatted and then select a number option (e.g. **General**) from the drop-down menu in the ribbon. This option is found in the 'Number' group on the **Home** tab (see below):

This method can be used in all versions of Excel, including earlier versions that do not have the ribbon:

- Select the cell(s) to be formatted.
Open up the 'Format Cells' dialog box by either:

- Right clicking on the selected cell(s) and selecting the
option from the right-click menu__F__ormat Cells...

- Using the keyboard shortcut Ctrl + 1 (i.e. press the Ctrl key and while holding this down press 1).

- Right clicking on the selected cell(s) and selecting the
- Ensure the
**Number**tab of the 'Format Cells' dialog box is selected. - From the
list, select the option__C__ategory:**General**(or**Number**). - Click
**OK**to close the 'Format Cells' dialog box.