Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Okey, this is a little hard to explain. I need to restrict entering the data in an excel cell, to a certain format, in this case to a time format which looks like, for example: 1:05 (h:mm).

Is there a way to format the cell so it can take only that kind of format ? So, for example, a user cannot enter 1,1 and get it automatically converted to 2,24.

This is probably possible but I don't really know how such operation is called, which makes the search difficult. So far my googlings have been fruitless.

share|improve this question
I've definitely seen sheets setup like this - though the ones I've used had forms that auto-started to populate the cells – warren Sep 7 '09 at 3:27
up vote -1 down vote accepted

This is actually pretty easy to do.

  • Right-click on the cell you want to restrict the format in
  • Choose "Format Cells"
  • In the "Numbers" tab (the one that it opens on), you will see a list of categories
  • For your example, choose "Time"

    Format Cells Window

  • On the right, you can choose different formats in which to have the time displayed
  • If you can't find a format that fits your need, choose "Custom" from the Categories list
  • Scroll down in the new box, and towards the bottom are some more time formats to choose from. There are some tips here on how to use the custom formatting.

Edit: To force users to only be able to enter matching text, I think you can use form controls. To access them, you need to enable the Developer tab in the ribbon. You can do this by clicking the Orb, "Excel Options" at the bottom of the menu, and then tick the third checkbox down, "Show Developer tab in the Ribbon".

Once the Developer tab is in the ribbon (to the right of the view tab), you will be able to insert Form and ActiveX controls in the spreadsheet. You have a LOT more control over these, but there's also more work involved. I've only ever used them in Word, where I'm able to limit text to only being in an allowable format.

Microsoft has a KB on How to use the form controls on a worksheet in Excel.

share|improve this answer
Yes, of course, I'm aware of formatting cell options :) But, as I wrote, if I try entering "1,1" in that cell, it will get converted to ... something. I wish to disable that, and only enable entering numbers in a format that looks like that formatted one. So one couldn't enter anything but things in xx:xx format. – Rook Sep 7 '09 at 1:19
Edited my answer to include some new information that might help. – Jared Harley Sep 7 '09 at 1:47
Hmm, looks like a bit of work, but I think I can live with that – Rook Sep 7 '09 at 2:11
that's a lot of work compared to @Lunatik's – warren Sep 7 '09 at 13:39

As you have found, in-cell number formatting can only get you so far; you need to use Data Validation to force an input to a specific format.

  • Excel 2007 (& likely 2010) - Data Tools group > Data Tab > Data Validation
  • Excel 2003 and earlier - Data > Data Validation

I've not got 2007 here so can only view/explain the 2003 dialog box, but I think this is similar if not identical in the newer versions. Select Time and enter the restrictions and input/error messages (if any), users will then be guided to input in the correct format.

alt text

Data validation can work pretty sweetly, as long as the data matches one of the predefined criteria sets. For anything more complicated you'd need to use a form.

share|improve this answer
that's a much better answer than the 'accepted' one, I think :) – warren Sep 7 '09 at 13:38
This seems easier, but you are forced to enter a start time and end time. If you are trying to show amount of time, rather than an actual time (ie, worked on project x for 1:03 h:mm), this won't work. – Jared Harley Sep 7 '09 at 13:43
I only showed the validation dialog with start and end time criteria, you can select any valid time format as the validation criteria if you want. – Lunatik Sep 7 '09 at 13:45
I'll try this one as well ... seems easier. Now, if only I could find it in this freakin version 2007 menu :( grrr ! – Rook Sep 7 '09 at 18:40
Data Tools group > Data Tab > Data Validation :) – Lunatik Sep 7 '09 at 20:37

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .