I have a worksheet used in HR to determine sick leave abuse. Formerly, the worksheet was set up so the user had to input time in [h]:mm. I had data validation so that the user had to enter the time with a colon. Our software has changed, and now all time is in decimals. How do I write a data validation so that the user must input time in decimals? I want a popup telling them to input it in decimal format if they try to enter it in h:mm.
Go to Data > Data Validation > Allow > Decimal
Don't forget to update the Error Alert Message
If I've helped U pls click on d *Add Reputation
Unfortunately, it's not that easy. When you click on decimal, you have to select from the drop-down box, and I have no idea how to specify it further.
Decimal.jpg
If you're referring to time then you can use Greater than and put in 0
Sorry, but that doesn't work. Everything I have tried will convert what is entered into a decimal instead of alerting the user to enter it correctly. I just did what you suggested and entere 1:45 (1 hour 45 minutes) using the colon, and it changed it to 0.07.
This is what you said:
How do I write a data validation so that the user must input time in decimals?
So you need to enter 1:45 as 1.45 (decimal)
The whole purpose of the validation is for when a user doesn't do it correctly - it will then prompt them to input it correctly. My users are used to putting in time as h:mm. Now we are wanting them to change what they are used to. This is a safeguard so that they do not enter the time incorrectly. As I said before, in the past if they entered time as a decimal (1.75 instead of 1:45 for 1 hour 45 minutes), the data validation would tell them their time was entered incorrectly and to input time as h:mm. Now I want the reverse.
so how do you want your users to enter 1:45?
Decimal [h]:mm format
0.572916667 13:45
0.072916667 1:45
They need to enter it as 1.75.
Last edited by lucy61176; 07-16-2015 at 03:44 PM.
Maybe it will help if you see what the previous worksheet looked like when the user did it opposite of what I am trying to accomplish. If the user inputed time using a decimal but really meaning to use a colon, such as 1.45 instead of 1:45, an alert would popup and tell them the time was inputted incorrectly and to input time as h:mm. (Ignore the total in the gray box.) I now need it to do the opposite but do not know how to do the data validation to accomplish this.
Hours-Minutes.jpg
Last edited by lucy61176; 07-16-2015 at 04:03 PM.
ok. so choose Custom and this formula:
=FIND(".",A2,1)>=LEN(A2)-2
Go to Error Alert tab then type: Must enter time as 0.00. Increase decimal places to your preference.
It says there is an error, and I copied and pasted what you suggested.
just click yes
What about entering 1.00 or 1? It doesn't allow that but it does allow less than 1, such as 0.5. How do I fix it so that it allows you to enter the value of 1?
if that is the case then you need to use this formula:
=A2=INT(A2*10000)/10000
that will allow a whole number to be entered in the cell
This appears to be it and what I have been looking for. Thanks for your help!
where are you entering the formula? what cells/column?
ok great. Please mark this thread as SOLVED and consider adding a reputation
I forgot to change A2 to the cell I was actually in so it works for me. Got it now. Thanks again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks