prevent user entering data in time format

[Graham Charlton]

I am creating a timesheet in excel and need to restrict the format users can enter data. I need them to enter the hours worked in a decimal format. i.e seven and a half hours to be entered as 7.5 This seemed quite straight forward using data validation by restricting entries to numeric values. the problem with this however is that it does not prevent users entering data in time format i.e. entering seven and a half hours as 7:30 which is causing problems when calculating formulas based on the entries. Is there a simple way to prevent users doing this?

Thanks,
Graham

 

[steve the fish]

This would work most of the time i believe:

=AND(LEN(A1)<6,ISNUMBER(A1))

 

[Graham Charlton]

Thanks Steve!