Data Validation Typed Time Issue

Lee J

New Member
Joined
Jun 30, 2020
Messages
45
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Attachments

  • Time Issue.png
    Time Issue.png
    29.4 KB · Views: 8
Sandy666, it needs to match the list that's why I used data validation and 01:00 is in the list
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi Sandy666, It needs to be a complete hour or hour and a half as per the list - that's the reason I used data validation.

Sorry if you already read the above before I edit it to this:

Hi Sandy666, I get where you are coming from but we have 700+ cells that might need to be filled in on a weekly basis, I just don't get why it will not work for all values included in the list :)
 
Last edited:
Upvote 0
you need formula but this is not my story, sorry

formula.png


you can test formula on your Time list but remember formula in DataValidation is treated as array formula
 
Last edited:
Upvote 0
Hi Sandy666, It needs to be a complete hour or hour and a half as per the list - that's the reason I used data validation.

Sorry if you already read the above before I edit it to this:

Hi Sandy666, I get where you are coming from but we have 700+ cells that might need to be filled in on a weekly basis, I just don't get why it will not work for all values included in the list :)
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
 
Upvote 0
This must be a bug but, try re-entering the times in your validation list, just the one's that won't work.
This fixes it for me!

All the cells in Red would not accept the value from the list in Col A
I re-entered the values in Col A and it now allows them!
1593525242720.png
 
Upvote 0
I tried with list (Time), without list, hard coded time in DV, with time as text, with formula and still the same for mentioned values
so I agree with @gaz_chops
maybe this is a problem with decimal point
btw. you defined list to select from list not entering manually so turn on warning : select from list don't enter manually
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top