Predefine the format which can be entered in an excel cell

Question

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.

Answer 1

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"

  

• 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.

Answer 2

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.

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.