Excel - minutes format

  • Thread starter Thread starter Deleted member 49549
  • Start date Start date
D

Deleted member 49549

Guest
Hi all, have come across a simple excel problem i cant google the answer too, so am asking here.

I have a time in a cell - eg 09:03:00. I wish to set up a custom format to display only the minutes, ie 03 for that time.
Variations of m, mm, M, MM don't work as excel thinks this is a month day format.

The closest i have got is [mm] format, but excel would display 543 for the above time.

I know i could use a formula to extract the minutes, or even some vb, but was wondering how to do this in a custom format without any other cells or formulae, as i am sure you should be able to do this. Surprised i haven't come across this limitation in the long time i have been using excel (or perhaps i have an have forgotten!)
 
Hi Neil - i know i can use a formula to do it (eg = MINUTE(a1) or =MOD(a1,60)

I was wondering how to display only the minutes via a custom format, in a similar way you can just display months or days by MMM or DD.
 
MS description of the minutes special format..


m - Displays the minute as a number without a leading zero.

Note The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.

[m] - Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss.

mm - Displays the minute as a number with a leading zero when appropriate.

Note The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.
 
neil_g said:
MS description of the minutes special format..


m - Displays the minute as a number without a leading zero.

Note The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.

[m] - Displays elapsed time in minutes. If you are working with a formula that returns a time in which the number of minutes exceeds 60, use a number format that resembles [mm]:ss.

mm - Displays the minute as a number with a leading zero when appropriate.

Note The m or mm code must appear immediately after the h or hh code or immediately before the ss code; otherwise, Excel displays the month instead of minutes.
Click to expand...

Yep, that is what i have found, just wondered if there is some sort of workaround as there often is with excel.
 
Years ago (Excel 2003) I would use MM : SS as my custom format - this should give you 03:00 - don't recall I managed to work out how to just display the minutes though:)
 
Last edited:
srichards said:
If the minutes are always the last 2 digits then is there just a way of showing the last 2 digits and nothing else? Assuming you'd want to display 03 for the 9.03 and you didn't care what hour it was. MOD is a remainder so it would make sense to try that first.

Looks like you can create your own custom number format: http://office.microsoft.com/en-gb/excel-help/create-a-custom-number-format-HP010342372.aspx
Click to expand...
thats the link i got the m, mm, [mm] descriptions from.
 
I don't think there's a way to do this without a formula.

But I'd be interested to know why you don't want to use a formula as I'm struggling to think of an advantage to using a custom format for this type of data - custom formats where you disregard a chunk of the data (reducing 09:03:00 to 03) can often cause confusion down the line when someone decides to use that cell to calculate a value and the hidden hours have their effect on the result!
 
trying to use mm etc appears to default to month =Minutes() is the easiest way to extract the minutes but as @Alastair asks, why don't you want to use this?
 
Hi all,

I wasn't looking for an answer to how to do this via formula as i knew how to do that way, but was surprised there didn't appear to be a way to do this via custom formatting and was looking for more info on that method.

Excel has so many workarounds for things you apparently cant do i thought there maybe a method of doing this, but apparently not.

Many thanks all for your help.
 
Did the solution I used to use in Excel 2003 which gives the result as 03:00 not work in your version of Excel? I have not got access to a later version to test it out.

See post #8 above
 
lostsoulal2 said:
Did the solution I used to use in Excel 2003 which gives the result as 03:00 not work in your version of Excel? I have not got access to a later version to test it out.

See post #8 above
Click to expand...
Hi lostsoul - mm:ss does give the result you would expect, but as soon as you use 'mm' on its own, unfortunately excel translates that as months.
 
Yes, that was an issue I encountered and never found a formatting solution, I did also use mm:s which gives 03:0 which looks a little less obvious? You will have to compromise and use a formula to get the desired result :).
 
lostsoulal2 said:
Yes, that was an issue I encountered and never found a formatting solution, I did also use mm:s which gives 03:0 which looks a little less obvious? You will have to compromise and use a formula to get the desired result :).
Click to expand...

Thats what i did in the end. But i like trying to solve problems in the limitations of excel that i come across and was surprised that there wasn't a workaround on this one.
 
You must log in or register to reply here.
Back
Top