Data Validation Typed Time Issue

[Lee J]

Good morning People,

Having an issues when trying to type a time (but only certain ones) into a cell i.e. 00:00 and 00:30 work but 01:00 does not when using a named list in data validation.

Image attached shows the issue and I have file ready to upload but not sure how to do this

Thanks in advance for your help,

Lee

 

[pjmorris]

In cell A2 of your Time table enter =A1+1/48 and copy down. All should then be well.

The cause of the problem is a rounding error. If you select your 01:00 in a cell and put this formula =Mod(A5, 1/48) somewhere else (where A5 is where you've used the dropdown to get 01:00 you get the result 3.46945E-17. Whereas, if you type 01:00 in another cell and point the formula at that figure you get 0 as the answer, which is correct.

​

 

[pjmorris]

To be honest, whatever you'll do for validation I (user) can enter whatever what I want and doesn't matter rules you defined
You can try with ComboBox with any vba but again - this is not my story, sorry

Sandy, how can that be true? ignoring the fact that the validation is intended to prevent inadvertent mistakes rather than intentional errors, simple protection on the sheet will prevent editing of the Validation rules. And for the avoidance of doubt the OPs problem is caused by a rounding error and his simplest solution is either to recalculate the Time list or use =Mod(A1,1/48)=0 as a custom validation rule. (it worked for me anyway).

 

[sandy666]

I didn't say there is no solution
I said I can insert inappropriate value to the Data Validation

 

[pjmorris]

how? especially if its protected

 

[sandy666]

we are talking about Data Validation feature not about sheet/cell protection
so simple drag-n-drop enough for most cases

 

[pjmorris]

I hdan't actually remembered that behaviour, and even turning on sheet protect doesn't stop it. However, to my mind that doesn't undermine the value of data validation, just something to be aware of. The fact that you could still put incorrect figures into the cell isn't a reason to not try to keep them correct.

PS - using my formula as a custom validation doesn't work. It seems the deeper workings of Excel create very fine differences (of the order of E-18 - about .3 of pico second!), which is very annoying.

 

[sandy666]

I think you should re-read post #16
I wont talk about formula because I use it on my own needs (very rare) or vba (I don't use vba in any form)

 

[pjmorris]

I did, I learnt nothing useful. You should learn some vba - it will seriously expand your capability.

 

[sandy666]

You should learn some vba

No chance , I hate vba