Determining maximum time value in mm:ss

Hi, I have an excel report into which I paste the maximum wait time for a number of queues, where the last column is the maximum value. Excel does not appear to like mm:ss (it turns the seconds into the minutes column and makes all the seconds zeroes) so I have managed to make this work using hh:mm but yesterday we had a call wait over 24 mins and now the value 24:41 appears as 0:41, presumable because you cannot have more than 24 hours in a day. The MAX value for the day no longer works and appears as 0:00.

What format can I use for these cells to make them appear as duration in mm:ss (and not a point in time) and that will allow me to get the maximum value for the rows above? I'm going quietly crazy with this so any help much appreciated.

Max Delay
16:36
13:28
5:28
7:39
24:41
11:38
12:30
:00
15:39
6:06
00:00

post a sample work book!

Formatting like mm:ss only affects the way the data is displayed, not how the data is interpreted. 12:45 will always be interpreted as 12 hours and 45 minutes however the cell is formatted.

If you want Excel to interpret that as 12 minutes and 45 seconds then you need to input as 0:12:45....but if you can't change the value to be imported you might have to settle for hours and minutes to stand in for minutes and hours. You can use a format of [h]:mm (with square brackets) which would allow the display of more then 23....

Actually, Excel deals with time values just fine. Perhaps you are entering them as hours/mins thinking you are entering min/sec.

If you post an actual workbook where we can see what you're entering directly into the workbook and highlight the formulas that are giving you unwanted results, we can resolve it for you I'm sure.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook. Make sure it's clear where the errors are and what you'd prefer the answer(s) to be.

I just changed the formatting of those cells to [h]:mm and I think it does exactly what you want.

Don't misunderstand me, I've helped you resolve the DISPLAY problem. The data is incorrectly entered for what you need it to be.

In the next column over, you could convert the values back to minutes with the formula:

=B3/60

...then format that as mm:ss

One "cludge!" you could do is to put 00:24:00 in a cell, copy it and paste special multiply over your data, you can then format as mm:ss, although you have to be aware of the ":00" type values!

incidentally, your sheet gives the correct maximum in Excel 2010!

Originally Posted by greyscale

What problems would it cause to leave it at [h]:mm?

The problems are that you are seeing one thing and it is actually another. The "problems" that could introduce are limited only by the universe and what unknown things you may try to do with the data later, or someone else.

Nothing may ever go wrong, too. But if it does, my only worry is that you won't even know that it has happened, something all too possible with data that looks correct but secretly is not.