How to add minutes and seconds together

In column I in my worksheet I have a list of timings in minutes and seconds.

These are transferred by formula to C and added together in E in a running total in Mins:Secs. In F these are then displayed in Hrs: Mins:Secs.

This works well to a point , but falls down when larger numbers are input into I. Anything over 24 minutes in fact. I do know whey this is , but despite my best efforts , I can't get the formatting correct to include minutes and seconds up to 60:00 without running into trouble. I thought I'd ask here as a fresh pair of eyes may see a way through.

I recognise that some of the current formatting in the attached file is there only because it works up to a point (<24:00) rather than for it's logical purity.

I'd be grateful if anyone can see a way to get the formatting in E and F displaying correctly. I've attached a sample file. Enter any timing in I over 24 in mm:ss format to see the effect.

Thanks in advance.

I notice that the values entered in column I are in hours:minutes and not minutes:seconds and that column I is formatted as time of day. Of course, a day is only 24 hours, so any value larger than a day (24 hours, which you are wanting to treat as 24 minutes) is not displayed correctly. Is there a reason you prefer to enter the values as hours:minutes rather than minutes:seconds?

I notice that your sample does not include any time values larger than 24 hours (24 minutes). Can you explain what goes wrong (other than an incorrect display) when you enter a value larger than 24 hours in column I? It seems to work correctly when I enter a value larger than 24 hours in column I.

The main solution I see is to enter the values as minutes:seconds in column I (which usually means you need to include the hours placeholder during data entry 0:5:17 for example). Then make sure to format the values and sums as elapsed minute as you are currently doing in E and F.

MrShorty - thanks you for your advice. I agree the formatting needs attention to produce more consistent results. I think when I was setting this up I happened upon a format structure which seems to produce correct output. I do realise that once over 24 it fails , for the reasons you identify. I'm really trying to have it produce the same format of output when the timings over 24 are used.

As an example , presently if I enter 42:10 (mm:ss) into I23 it shows 00:18 as a result. I'm struggling to have all the columns reflect the format I'm searching for because a single change seems to throw them all out. I'm clear on what I'm trying to do , but can't find the correct logic in terms of the format balance to make it work. At the moment at least.

I'd be grateful if anyone can see a way to get the formatting in E and F displaying correctly

What is the correct format then? I only see formulas, but nowhere manually entered the expected results. Please add some of these to your sheet.

Sorry if that wasn't clear. The display of the results in E should be mm:ss and in F hh:mm:ss. This is as displayed in the sample file.

The issue is with taking into account timings above 24 in I.

If you manually enter a timing into I below 24 it will be successfully accounted for in D , E and F. If you enter a timing in I above 24 then it isn't reflected correctly.

I appreciate the formatting is not accurate somewhere in the sheet , and I'm struggling to get accurate running total with timings above 24. This is the reason for my post.

Thanks for your help.

Thanks Pete_UK. Yes , that's helpful. I can see the logic.

Presently the timings in I are entered remotely in the form 42:10:00. I'll need to find a formula to change these to 00:42:10.

davsth - yes , that does work. It accommodates for the issue in I.

If I enter for example 42:10 in I23 then it displays as 18:10. With your amendments in place , it is reflected in C, E & F as 42:10 , so gives a correct calculation.

With a reformat of the display in I , this will solve the issue I think..

Thanks for the feedback and rep. .

The issue is with taking into account timings above 24 in I.

In your example there were no times above 24:00 in column I.

And I don't see errors in column E.
I see only errors in column I: Solution: Please custom format column I: [hh]:mm
And in column F: Solution: Please custom format column F: [h]:mm:ss

See sample file and see post #6 of Pete Also.

Thanks HansDouwe. That's working.

BTW , you are correct there were no times above 24:00 in column I in the example , but if you enter any number above 24:00 into I then you will see the issue.

You are Welcome!

Thanks for the feedback and glad to have helped .

Thanks guys for your time and expertise. Problem solved.