Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

share|improve this question
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

5 Answers 5

up vote 3 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.

share|improve this answer
    
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.

share|improve this answer
1  
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

share|improve this answer

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
share|improve this answer

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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