Formula to subtract duration from fixed time

**TopDog0310** · 09-19-2013, 06:30 PM

Hi all,

I am looking to create a seemingly simple formula to calculate time subtracted from a set time (mm:ss from mm:ss).

Everywhere I look seems to solve the issue from a time stamp point of view.

My problem, more specifically, is to create a formula that can be updated per football player to calculate their active game time, against their inactive time, from the predetermined value of "90:00".

So if a player plays for 45:50, their inactive duration should be 44:10.

Firstly, my issue is a cannot find a suitable format to display the time in this manner - results are completely unexpected; I thought that the best would be Custom "00 "min". 00 "secs"" - but this still gives me the second issue I have...

I cannot seem to unlimit the minutes (90:00 instead of 1:30) or cap the seconds to 0.59 instead of 0.99, so using the above example, I get 44:50 inactive game time.

Ideally, I would like to use a formula such as

=SUM(90-C10) [where C10 is the variable of mins and secs played - updated weekly]

I thought I am fairly handy with excel, but it appears that this one has got the better of me!

Thanks in advance for your help... and also, this is my first post, so a big hello to you all.

**6StringJazzer** · 09-19-2013, 10:37 PM

Welcome to the Forum!

I am not clear on why you can't just do this:

Formula:

Please Login or Register  to view this content.

Do you ever have to actually display 90:00 anywhere? If so there is a somewhat convoluted way of doing this, but you can't do it with simple Excel formatting.

If the value 1:30 is in B10 then

Formula:

Please Login or Register  to view this content.

will show 90:00 (or any other value for minutes).

See attached.

Edit: I just noticed that in my example I reversed the seconds portions in the two numbers that you provided, but of course it still works. Just don't get confused if you compare it directly to your post.

**TopDog0310** · 09-20-2013, 12:49 AM

Thanks 6String,

Thanks for the welcome and help... it seems to have half sorted the issue, but when I then change D10 "Play Time" to say 82:50 - all the formatting goes haywire...

The issue now is that it appears as "1:22:50" (hh:mm:ss) or "22:50" (mm:ss) - which isn't ideal as preferably it would be "82:50". Have you got any ideas (or anyone) as to fix this?

Thanks again in advance.

**6StringJazzer** · 09-20-2013, 12:51 PM

I was not clear on your problem. It might help if you attach a file. I thought you were always using 90:00. If you have to type in a duration which can vary, and you always want it in minutes, then my solution isn't right for you. In my file the idea is that you enter it in cell B10 as hours:minutes and then it is displayed in C10 as minutes:seconds. But if you type directly into C10 you wipe this out.

I know of no way that you can type in 90:00 and have it interpreted as 90 minutes, unless you use macros. I have cooked up a sample that modifies my first file. You type in the total duration and playing time where shown, in the form mmm:ss. For this to work I set the format of columns A and B to "Text". You can type in any number of minutes less than 1440 (that is, less than 1 day); seconds must be less than 60. Then there is a user-defined function written in VBA that will look at that string and convert it to time to do calculations. The UDF is called STRINGTOTIME. If the time that you typed in can't be interpreted as a valid time, the result of the function will be "#TIME!". Another UDF called TIMEINMINUTES will show a time as mmm:ss, using the same reasoning in reverse--if the time is over 1 hour, it will still show in minutes.

**TopDog0310** · 09-20-2013, 02:14 PM

Hi 6 String...

Let me try to explain again... lol.

I am using an app on my tablet to log incidents in a game (and manage subs that have been made). With that it calculates the mins:secs each player plays.

When I export the raw data as a CSV - it tells me in sentence form, the mins:secs each player played.

With this, I hope to be able to enter the mins:secs played into a cell, and below it, a formula will calulate the time (from 90mins:00secs) that he/she didn't play.

Once it has done that - it will in turn automatically update an exploded doughnut chart for a visual for the players to see their game time vs bench time. This by the way I have sorted without a problem.

It is merely the formula and cell format I am struggling with. I have uploaded both the template I am working with, and an example of the finished look I am aiming for.

I hope this is clearer - if not please let me know and I will be more than happy to try and explain again...

Once again, I cannot thank you enough for you persistent help...

**Greg M** · 09-20-2013, 04:35 PM

Hi TopDog,

Take a look at the attached modified version of your workbook and see if it does what you want.

The workaround makes use of the convenient fact that the number of seconds in a minute is the same as the number of minutes in an hour!

The User enters the Match duration and the length of time played (in the green cells) as e.g. 90:00 and 44:30 - Excel will "think" that these values are Hours:Minutes, but the values in the blue cells will be displayed as if the values in the green cells were entered as Minutes:Seconds.

As per your original version, the doughnut graph shows the percentage Played/Unplayed times.

Hope this helps - please let me know how you get on.

Greg M

**TopDog0310** · 09-20-2013, 05:44 PM

Hi Greg,

Thanks very much for your help with my problems... such a simple solution - even if not exactly mm:ss - it's the same thing.

I can know close the thread... but before I do, can you please offer another piece of advice?

Using the same template; once the CSV has been exported, it automatically inserts the player's name into cells B6 & B7.

Is there anyway that I can link the name that updates into here, to only a part of the text box - used as a header??

Header Text Box Format:
dd.mm.yy - player name vs opponent team

I can manually update opposition and date - as the CSV does not store this information... As with most excel files, the idea is to remove the labour out of everything lol... is there anyway in which once the CSV is exported, it takes the name from B6 or B7 and inserts it as part of the Text Box that heads the Sheet?

Kind Regards

any help is much appreciated - as is your previous post.

**Greg M** · 09-20-2013, 09:26 PM

Hi again, TopDog!

Many thanks for the message you sent me re the worksheet Header Box.

Take a look at the attached and see if I've correctly understood what you have in mind.

Enter the Player's name in Cell B6.

Click on the button in the top left-hand corner of the worksheet - a UserForm will be displayed.

On the UserForm, enter the Match Date and the name of the Opponent Team, and then click on the OK button.

The Date, Player Name and Opponent Team Name will then be entered automatically into the Header TextBox on the worksheet.

Hope this helps - please let me know how you get on.

Regards,

Greg M

**TopDog0310** · 09-21-2013, 04:16 AM

Greg, 6 String

Thank you so much! You clearly are Excel Gods amongst us mere mortals!

Been so much help... with ease and efficiency!

Greg, it would perhaps be great to physically know how you did that with the Header Text Box... is that "Macro" something?

But as far as this thread goes, problem solved.

Thanks again guys

**Greg M** · 09-21-2013, 07:49 AM

Hi TopDog,

Yes - entering the required text into the Header TextBox is achieved using VBA routines (macros).

Press Alt+F11 to enter the VBA Macro Editor and you can view the code that's used.

There are two main routines - one displays the UserForm, and the other takes the Team Name & Date from the UserForm and the Player Name from the worksheet cell, creates the required text string and then enters that string in the Header TextBox.

There is also a set of routines associated with the UserForm - these check that appropriate values have been entered in the TextBoxes and return those values to the routine which displayed the UserForm.

Hope this gives you at least a little insight into what's going on behind the scenes!

Best regards,

Greg M

**TopDog0310** · 09-21-2013, 12:55 PM

Thanks so much to you both.

I certainly know who to turn to for resolution of any future excel problems

**Greg M** · 09-22-2013, 04:19 PM

Hi again, TopDog,

Many thanks for your feedback - glad I was able to help.

Regards,

Greg M

Formula to subtract duration from fixed time

LinkBack

Thread Tools

Rate This Thread

Display