up vote 2 down vote favorite
share [g+] share [fb]

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

link|improve this question
100% accept rate
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
feedback

2 Answers

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

link|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
feedback
up vote 2 down vote

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.

link|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
feedback

Your Answer

 
or
required, but never shown
discard

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