Calculating hours & minutes from time start and end.

[Dietmar Weise]

Time.jpg
The B and C columns are formatted as custom hh:mm.
The calculation for the D2 is '1-B2+C2'. This gives me the hours and minutes.
But when I add up the D column, it reads 01:10 in stead of 25:10.
Which formulas do I use to correct this issue?

[Special-K]

Change the custom format type to [h]:mm:ss

[Dietmar Weise]

Thanks Special-K, but this also is not working.
I have attached 3 samples of different formats and the hours don't add up to 25:10 (25 hours, 10 min)
If the total is under 24 hours, it shows the correct hour total, but when the hours exceed 24 hours, it starts from 00:00. (D7 only)
Time2.jpg

[AliGW]

Will you please attach a sample Excel workbook? We are not able to work with or manipulate a picture of one.

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential data is removed or replaced with dummy data first (e.g. names, addresses, E-mails, etc.).

4. Try to avoid using merged cells as they cause lots of problems.

Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

Please pay particular attention to point 2 (above): without an idea of your intended outcomes, it is often very difficult to offer appropriate advice.

[Special-K]

Thanks Special-K, but this also is not working.
I have attached 3 samples of different formats and the hours don't add up to 25:10 (25 hours, 10 min)
If the total is under 24 hours, it shows the correct hour total, but when the hours exceed 24 hours, it starts from 00:00. (D7 only)

Cell I7 is clearly more than 24hrs and therefore works.

Copy the format of cell I7 to cells D7 and N7 using the Paintbrush icon.

[Dietmar Weise]

My original spreadsheet is based on the sample
The total of hours should add to 24:15 hours, not 00:15.

In the original spreadsheet, I only insert data in the 'Times Input' sheet, the totals should work out according to the formula I battle with.