ExcelFunctions.net

Search Site:

Related Page:

Seconds To Time In ExcelThe following examples convert a time to a decimal (i.e. to a number of hours, minutes or seconds) using two different methods.

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

The simplest formula is shown below. In this case, the time is converted to hours by simply multiplying it 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*.

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

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.

To convert a time to minutes, the time is multiplied by 1440, which is the 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*.

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

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 seconds in Excel.

The easiest way is to simply multiply the time by 86400, which is the 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*.

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

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 have the Excel 'General' format. To do this:

- Right click on the cell(s) to be formatted
- Select the option
__F__ormat Cells... - Ensure the
**Number**tab is selected in the window that pops up - Select the option
**General**from the list of Categories and click OK