Hi All,
I have a sheet that I need users to be only able to enter time in hh:mm format or the word hol in cells.
I can do either or in the data validation function but need a way of doing both at the same time.
i hope I explained myself correctly.
Thanks Alot
Gavin
1) You format the cells in question with a number format that is TIME and in the format of hh:mm so it automatically displays correctly regardless of how they enter it.
2) TIME value are in reality fractions of the number 1 formatted to display in our familiar time formats. 0.5 for instance is noon, 0.25 is 6am, and 0.75 is 6pm. So a DV formula need only test that the value entered into the cell be either a number between 0 and 1 or the word "Hol".
Allow: Custom
Source: =OR(AND(A1>0, A1<1), A1="hol")
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks