by Svetlana Cheusheva, updated on

*The article shows a few tips to calculate and display times that are greater than 24 hours, 60 minutes, 60 seconds.*

When subtracting or adding time in Excel, you may sometimes want to display the results as the total number of hours, minutes or seconds. The task is a lot easier than it may sound, and you will know the solution in a moment.

To show a time interval of more than 24 hours, 60 minutes, or 60 seconds, apply a custom time format where a corresponding time unit code is enclosed in square brackets, like [h], [m], or [s]. The detailed steps follow below:

- Select the cell(s) you want to format.
- Right click the selected cells and then click
*Format Cells*, or press Ctrl + 1. This will open the**Format Cells**dialog box. - On the
*Number*tab, under*Category*, select**Custom**, and type one of the following time formats in the*Type*box:- Over 24 hours: [h]:mm:ss or [h]:mm
- Over 60 minutes: [m]:ss
- Over 60 seconds: [s]

The following screenshot shows the "over 24 hours" custom time format in action:

Below are a few other custom formats that can be used to display time intervals exceeding the length of the standard time units.

Description | Format code |

Total hours | [h] |

Hours & minutes | [h]:mm |

Hours, minutes, seconds | [h]:mm:ss |

Total minutes | [m] |

Minutes & seconds | [m]:ss |

Total seconds | [s] |

Applied to our sample data (Total time 50:40 in the screenshot above), these custom time formats will produce the following results:

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

1 | Description |
Displayed time |
Format |

2 | Hours | 50 | [h] |

3 | Hours & minutes | 50:40 | [h]:mm |

4 | Hours, minutes, seconds | 50:40:30 | [h]:mm:ss |

5 | Minutes | 3040 | [m] |

6 | Minutes & seconds | 3040:30 | [m]:ss |

7 | Seconds | 182430 | [s] |

To make the displayed times more meaningful to your users, you can supplement the time unites with the corresponding words, for example:

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

1 | Description |
Displayed time |
Format |

2 | Hours & minutes | 50 hours and 40 minutes | [h] "hours and" mm "minutes" |

3 | Hours, minutes, seconds | 50 h. 40 m. 30 s. | [h] "h." mm "m." ss "s." |

4 | Minutes | 3040 minutes | [m] "minutes" |

5 | Minutes & seconds | 3040 minutes and 30 seconds | [m] "minutes and" ss "seconds" |

6 | Seconds | 182430 seconds | [s] "seconds" |

Note. Although the above times look like text strings, they are still numeric values, since Excel number formats change only the visual representation but not the underlying values. So, you are free to add and subtract the formatted times as usual, reference them in your formulas and use in other calculations.

Now that you know the general technique to display times greater than 24 hours in Excel, let me show you a couple more formulas suited for specific situations.

To calculate the difference between two times in a specific time unit, use one of the following formulas.

To calculate hours between the start time and end time as a **decimal number**, use this formula:

(*End time* - *Start time*) * 24

To get the number of **complete hours**, utilize the INT function to round the decimal down to the nearest integer:

`=INT((B2-A2) * 24)`

To calculate minutes between two times, subtract the start time from the end time, and then multiply the difference by 1440, which is the number of minutes in one day (24 hours*60 minutes).

(*End time* - *Start time*) * 1440

To get the number of seconds between two times, multiply the time difference by 86400, which is the number of seconds in one day (24 hours*60 minutes*60 seconds).

(*End time* - *Start time*) * 86400

Assuming the start time in A3 and end time in B3, the formulas go as follows:

Hours as a decimal number: `=(B3-A3)*24`

Complete hours: `=INT((B3-A3)*24)`

Minutes: `=(B3-A3)*1440`

Seconds: `=(B3-A3)*86400`

The following screenshot shows the results:

Notes:

- For correct results, the formula cells should be formatted as
**General**. - If the end time is greater than the start time, the time difference is displayed as a negative number, like in row 5 in the screenshot above.

To add a desired time interval to a given time, divide the number of hours, minutes, or seconds you want to add by the number of the corresponding unit in a day (24 hours, 1440 minutes, or 86400 seconds), and then add the quotient to the start time.

Where N is the number of hours, minutes, or seconds you want to add.

Here're a few real-life formula examples:

To add 45 hours to the start time in cell A2:

`=A2+(45/24)`

To add 100 minutes to the start time in A2:

`=A2+(100/1440)`

To add 200 seconds to the start time in A2:

`=A2+(200/86400)`

Or, you can input the times to add in separate cells and reference those cells in your formulas like shown in the screenshot below:

To **subtract times** in Excel, use similar formulas but with the minus sign instead of plus:

The following screenshot shows the results:

Notes:

- If a calculated time displays as a decimal number, apply a custom date/time format to the formula cells.
- If after applying custom formatting a cell displays #####, most likely the cell is not wide enough to display the date time value. To fix this, expand the column width either by double-clicking or dragging the right boundary of the column.

This is how you can display, add and subtract lengthy time intervals in Excel. I thank you for reading and hope to see you on our blog next week!

Table of contents