# 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 ?

Thanks, Dani

-
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

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.

-
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

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`

-

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
``````
-

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.

-

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

``````=HOUR(A1)*60+MINUTE(A1)
``````
-