Formatting a cell to only display the minutes part of the time

Question

I've have a cell with some time value, e.g. 12:30. I would like have it displayed as 30.

I only want to work with formats (i.e the "representation"). So no VBA, no extra transformations.

A tried to use the format mm, but Excel is thinking that I want the months. Using the format mm:ss results in 30:00.

So, indeed, mm stands for both "months" and "minutes".

Any ideas?

PS: My Excel is in English, but the formats are in French ("months"="mois" in French => mm as well; "minutes"="minutes" in French => mm also.)

Answer 1

To be honest it's probably easier to use =MINUTE(). If you don't want the hours and seconds then you could always copy the minute info and paste as text, then delete the full time column.

You can convert the 24h time to minutes using the [m] format but then you will have to subtract the hour, eg

13:45 --> 825 minutes --> 825 - (13*60) = 45 minutes

Answer 2

I know it is an old question, but here is how I solved it:

I created a helper column using hours and minutes (so that excel recognizes that it should be minutes). Also, to make sure, I added double "hh" and double "mm" to ensure that it will be the same on all cells of the column of my table:

In A2 I have the time, so:

in B2: =TEXT(A2;"hhmm")

And then I converted it to minutes in a seperate column:

in C2: =RIGHT(B2;2)

If you then want some text after your minutes you can do it the following way:

in C2: =RIGHT(B2;2)&"minutes"

Hope this helps someone :)

Answer 3

I know this is a super old post, but am posting a solution I discovered, for anyone who stumbles upon it.

I'm not sure if this is a new feature/update to Excel but I discovered that to get minutes you can simply enter [mm].

i.e. I have created a custom format [mm] "mins" , which gives me the time, in minutes, followed by the word mins. But is still a valid time to use in calculations.

0:30:00 -> [mm] "mins" -> 30 mins

Answer 4

Here is a sample (for cell D1).

Say D1 has a formula that displays time:

and we only want to display the minutes. Put the following event macro in the worksheet code area:

Private Sub Worksheet_Calculate()
    With Range("D1")
        v = Minute(.Value)
        f = .NumberFormat
        If InStr(.NumberFormat, ";;;") > 0 Then
            ActiveWorkbook.DeleteNumberFormat NumberFormat:=f
        End If
        .NumberFormat = Chr(34) & v & Chr(34) & ";;;"
    End With
End Sub

Every time the worksheet is calculated, the macro adjusts the format of D1 to reflect only the minutes. (the code also removes previous custom number formats to prevent format everflow)

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

Macros must be enabled for this to work!

Answer 5

Your requirement is

• only want to work with formats (i.e the "representation").
• therefore no VBA
• no extra transformations (aka no formula to transform the value).

My solution is therefore modifying the configuration on the operating systems, in order to prevent the wrong interpretation of mm (month instead minute).

Here is how to proceed:
check your Windows Settings. You may have a custom date Format which prevent the right formatting in Excel.

1. Open Control Panel.
2. Click on the Clock, Language, and Region link.
3. Click on the Change date, time, or numbers formats link.
4. Under the Formats tab, click on the Additional settings button.
5. Click on the Time tab.
   Under "Time formats," you'll notice that now you can change various aspect of the time format. Check if you have any special time format defined there.
6. Click on the Date tab.
   Under "Date formats," you'll notice there is more flexibility to change the date format. Check if you have any special date format defined there.
7. Reset to restore the system default settings (button on the bottom)

Basically it should work as you have defined. On my System (german language ) it formats in minutes correctly.

+------------------+------------------+
|      on screen   |  Custom format   |
+------------------+------------------+
| 43278.58194      | Standard         |
| 27.06.2018       | TT.MM.JJJJ       |
| 27.06.2018 13:58 | TT.MM.JJJJ hh:mm |
| 58:00            | mm:ss            |
| 58               | mm               |
+------------------+------------------+