Entering Time data in excel in mm:ss format (without the leading 0: for the hours)

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 ?

Solution 1:

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.

Solution 2:

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.

Solution 3:

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