Format Time Only 3

[tj007]

Hello

I have cells in a form formatted as custom time 8:30 [h]:mm which fine. However, when someone enter 8.00, (decimal instead of colon) the calculations within the form are incorrect. Is there a way to format the cell so that only time can be entered.

I have protected the sheet, but these cells must be unlocked for the user to enter time 8:30 and not 8.30.

Thanks

 

[Blujacket]

I don't have a solution but you've described a small part of why some companies/government agencies have gone to the decimal system of .10 [1/10th of an hour] equals 6 minutes, easier to make time calculations.

 

[BlaineR]

Hi tj,

1. Select the cells where your inputs go.
2. Select Data > Validation from the menu.
3. Under Settings, select
Allow: Time
Data: between
Start Time: 00:00
End Time: 23:59

4. Click OK.

Now, users will only be able to enter times; inputs with decimals, etc. will be disallowed.

It's not a bad idea when you are in Data > Validation box to click on the Error Alert tab and compose a message to indicate to your users what the correct format is (e.g. "Please enter a time in the format hh:mm and be sure to place a colon between the hours and the minutes&quot so you don't make them go crazy trying to figure it out.

Hope that helps!

Blaine

 

[KenWright]

Only thing to be aware of with Data Validation is that it is easily bypassed (or broken). If someone copies data from another cell and then pastes it into the cell with DV, then the DV is lost.

Regards
Ken..............

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all

----------------------------------------------------------------------------

 

[TonyJollans]

Hi Ken,

I never realised that but, thinking about it, it's just part of cell formatting that often gets copied when you don't want it. It really makes what was anyway a fairly weak feature virtually useless.

That's something I've learnt today, so star from me.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[tj007]

BlaineR & KenWright submitted pertinent information here. The data validation will work fine even if the user copy and paste. Only the input message will incorrect. For example, the cells are reg ot, sick leave, annual leave, and other hours. If the user copy and paste the data from the reg ot to the sick leave cell, the error message will be correct, but the input message will be incorrect. I copied only the data from one cell to another by highlighting the input, and it worked fine without transferring cell attributes.

A Star for BlaineR & KenWright