I want to enter time data into excel sheet in mm:ss format. The problem is - that although the cell format is mm:ss, if I enter the data like this:

12:33 the actual value stored is 12 hours and 33 min. if I want 12 min and 33 sec, I need to enter 0:12:33

Is there a way to make the cell accept 12:33 as mm:ss ?

  • 2
    To display minutes larger than 59, format the cell as [MM]:SS. Like: 02:10:45 would then display as 130:45 instead. But apparently that does not work for inputs. – Arjan Jan 20 '11 at 9:16
up vote 5 down vote accepted

Time is universally entered in Hours:Minutes:Seconds format, so you can't change this entry format. You can obviously change the display format, but unfortunately you'll need to abide by the entry format.

  • it looks like there is no solution to my problem, as even using scripts won't help, since the formatting masks what the user actually types. – Dani Jan 20 '11 at 12:50

As answered by Steve, Excel interpret user input 12:33 as 12h33m. You cannot change this behavior of how Excel accepts user input.

As you mentioned in comment, users would input 12:33 but meaning 12m33s.

Here is an workaround solution to get the correct value for calculation.

  1. Format cell A1 (user input, eg. 12:33) as [h]:mm
  2. Enter formula =A1/60 in cell B1
  3. Format cell B1 as [m]:ss

The displayed value should be the same in A1 & B1. However, B1 will be the actual value you want.

  • 2
    Nice. Or: format the input as a string/text value, and use something like TIMEVALUE to convert it. – Arjan Jan 24 '11 at 16:35
  • 1
    The displayed values in A1 & B1 aren't the same if mm>23 – Alaa M. Jul 13 '17 at 7:20
  • 1
    @AlaaM. thanks for your finding. the answer is updated to fix the issue. use [h] and [m] – wilson Jul 17 '17 at 2:45

There is a solution! Place this VBA code in your worksheet module, i.e.:

  1. Open the Visual Basic Editor (Alt-F11)
  2. In the top left treeview, double click on the sheet you want to enter your times
  3. In the central code panel, place the below code.
  4. Close VBE

Use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value < 0 Or Target.Value > 1 And Target.NumberFormat <> "h:mm" Then Exit Sub
    Application.EnableEvents = False
    Target.Value = Target.Value / 60
    Target.NumberFormat = "mm:ss"
    Application.EnableEvents = True
End Sub

In case you already formatted the range you're entering the data in, use this line instead as the first line:

If Target.Value < 0 Or Target.Value > 1 Then Exit Sub

Note that this will change the value and format - every time you enter either a time - or something that is between 0 and 1! If you want to restrict it to a certain column, add this line:

If Target.Column <> 3 Then Exit Sub

or this line to restrict it to a certain range

If Intersect(Target, Range("A2:A100") Is Nothing Then Exit Sub

Easiest way to work out hours completed

Format the Cell (A1) hh:mm

Input as normal (07:22)

Format the Cell (B1) hh:mm

Input as normal (16:00)

Format the Cell (C1) h:mm

Input all data into cell (C1) the following: =(A1+B1)/60

Highlight the cell(s)/column which you want as Duration, right click on the mouse to "Format Cells". Go to "Custom" and look for "h:mm" if you want to input duration in hour and minutes format. If you want to include seconds as well, click on "h:mm:ss". You can even add up the total duration after that.

Hope this helps.

I find it best to enter the minutes in column A then the seconds in column B. Then you can Sum the columns. All as normal number format.

Next convert seconds > 60 to minutes by dividing the sum in col B by 60 and add the INT to the Col A Sum and leave the remainder MOD in col B.

Alternatively you can have col C as decimal number by having a formula = (A1+(B1/60)) will give decimal minutes. At the bottom of Col C do a sum then apply a formula to convert the decimal portion back to Seconds as follows say C22 is where the sum is held then in C23 enter formula =MOD(C22,1)*60.

Now in A23 enter formula =INT(C22) to show Total minutes including those from the sum in C22.

I've had the same issue and here's the solution I've found: Enter the formula using the TIME function, which creates a time matrix as =TIME(H,M,S) and format it as you please. You still have to enter 0 hours every time because maths but this makes the whole thing infinitely more workable.

Solution to above. When using the h:mm format, excel will not let you go above 23. Add brackets to the hours in your format [h]:mm and you are free to go above 23.

To calculate the value in seconds, set the cell to "general" number format and then type the following formula


Your Answer


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.