Data validation

[yahozna]

Is it possible to use data validation to ensure that data is entered in the specific format of mm:ss.00
I would like to generate an error message if a different format is used.

 

[Gerald Higgins]

In Excel 2003, on the Data Validation dialog box, there's an option for Time, which you can then refine (if you want) by setting upper and lower limits.

You can also specify an appropriate Input Message, and a separate Error Alert message.

BUT, this won't actually control the format as such.
Users will still be able to enter a value between the specified limits, and format it as something else (for example currency).

To stop this, you'll want to format the cell as time, and consider protecting the worksheet to prevent changes to formats.

 

[yahozna]

It's a Sports Day spreadsheet so times entered will range from 00:12.5 to 10:00.0 in other words 12 and a half seconds up to 10 minutes or so.

problem is if someone entered 14.5 rather than 00:14.5 for 14 and a half seconds it would register as 12:00.00

 

[Gerald Higgins]

problem is if someone entered 14.5 rather than 00:14.5 for 14 and a half seconds it would register as 12:00.00

OK, so you can get round this specific problem to a certain extent by setting upper and lower limits in the data validation.

Set the upper limit to, say, 20 minutes, and the lower limit to 0, or some other low number.

 

[yahozna]

OK, so you can get round this specific problem to a certain extent by setting upper and lower limits in the data validation.

Set the upper limit to, say, 20 minutes, and the lower limit to 0, or some other low number.

Thanks, that works fine!