4

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.)

5
  • Apparently, what you are requesting seems to be not possible since a hh part is needed (leading the mm) for Excel to display minutes instead of months, and thus, defeating the intended original purpose. However, the function MINUTE used on the time cell, may come in handy where you need the result. But that involves "extra transformation", I'm afraid.
    – DroidW
    Jun 26, 2018 at 10:01
  • Does the cell contain a constant or a formula ?? Jun 26, 2018 at 11:55
  • Yes it contains a formula. But the result must remain a valid time. In the meantime, I've added an extra row with a formula to extract the minutes. But this is not nice.
    – lvr123
    Jun 26, 2018 at 13:06
  • Did you try upper case M for month? Apr 2, 2020 at 12:16
  • This is extremely bad functinoality by Excel. Actually if the value in the cell is only time (12:30) and you set formatting to mm then Excel changes the cell value to a full date/time with a null date (month:1 day:0 year:1900), and results in displaying only 01 as the month. In my opinion setting a format should NEVER change the cell value!
    – awe
    Jun 8, 2023 at 10:19

5 Answers 5

2

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
1
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 :)

1
  • Well, it is old, but it is still an issue in Excel... Thanks for a good workaround. You can actually do 2 things in one field so you only need B2: RIGHT(TEXT(A2;"hhmm");2)
    – awe
    Jun 8, 2023 at 12:32
2

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
1
  • 1
    This converts hours to minutes. It only works if hours is 0. In the case of question where the value is the time of day 12:30, [mm] will result in 750 (12*60 + 30)
    – awe
    Jun 8, 2023 at 10:05
0

Here is a sample (for cell D1).

Say D1 has a formula that displays time:

enter image description here

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

enter image description here

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!

3
  • 1
    Nice code. Although not working 100%. And not flexible. Every time you want to add the format on a cell, one have to adapt the VBE code or get tricky !
    – lvr123
    Jun 27, 2018 at 11:32
  • Microsoft has implemented contextual number formatting. The same format shortcut means different things depending on the context. First time I see that in a programming language.
    – lvr123
    Jun 27, 2018 at 11:33
  • @lvr123 The usual approach is to use two cells and a formula. Jun 27, 2018 at 11:41
0

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               |
+------------------+------------------+
3
  • You're pointing to system formats in Windows. Question is about formatting a time in Excel. In Excel both month and minute is formatted with lowercase m, which causes the problem because Excel requires that the format includes h: and/or :s for it to be recognized as minute instead of month.
    – awe
    Jun 8, 2023 at 10:02
  • yes because the requirement is not to have any transformation of the value (VBA or formula). Only the representation.
    – visu-l
    Aug 17, 2023 at 8:56
  • I do not have set up a custom format. It is standard norwegian. The problem is that the formatting in Excel does not distinguish between lowercase m and uppercase M.. It just formats minute or month based on context
    – awe
    Sep 19, 2023 at 10:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .