I want a column that only accepts values of the format 00:00 through 23:59. I would like for this to be accomplished using cell validation.

=IF(AND(LEN([Start Time])=5,MID([Start Time],3,1)=":",VALUE(LEFT([Start Time],2))<=23,VALUE(RIGHT([Start Time],2))<=59),TRUE,FALSE)

For my setup, (SharePoint on line (Office 365)) the above answer is close, however it does not display my custom 'user message' in some cases of invalid data. For instance if the user attempts to enter '9' or '9:00' the displayed error message is 'The validation formula has evaluated to an error.', whereas '09:AA' or '09:60' results in my custom 'user message' of 'Enter the Start Time in 24 Hour time using the format HH:MM e.g. 09:00'

I believe this is because, for example MID("0000",5,1) returns "" (an empty string), and CODE("") results in an error (like #Value! in Excel); hence if the length of the data entered is less than 5 characters, such an error will ultimately be encountered at some point during the validation, causing the undesirable error message instead of the desired one.

My fix for this is to append a string of five (invalid) characters to the input to be assessed such that, per the above example, MID("0000"&"AAAAA",5,1) returns "A" and subsequently CODE("A") returns 65 (as opposed to an error) which evaluates to FALSE when tested against '<58', thereby giving an overall result of False and displaying my desired error message.

Therefore, adapting the above solution:-

=(LEN([Start Time])=5)
+(MID([Start Time]&"AAAAA",3,1)=":")
+(CODE(MID([Start Time]&"AAAAA",1,1))>47)
+(CODE(MID([Start Time]&"AAAAA",1,1))<51)
+(CODE(MID([Start Time]&"AAAAA",2,1))>47)
+(CODE(MID([Start Time]&"AAAAA",2,1))<58)
+(CODE(MID([Start Time]&"AAAAA",4,1))>47)
+(CODE(MID([Start Time]&"AAAAA",4,1))<54)
+(CODE(MID([Start Time]&"AAAAA",5,1))>47)
+(CODE(MID([Start Time]&"AAAAA",5,1))<58)
+IF(CODE(MID([Start Time]&"AAAAA",1,1))=50,(IF(CODE(MID([Start Time]&"AAAAA",2,1))>51, 0, 1)), 1)=11

If the user has entered a string of length = 5, then the additional invalid characters, added purely for validation, are already beyond the 'reach' of the evaluation, and hence do not come into play.


This can be accomplished by using the single line of text type and using the following validation:

=(LEN([Start Time])=5)
+(MID([Start Time],3,1)=":")
+(CODE(MID([Start Time],1,1))>47)
+(CODE(MID([Start Time],1,1))<51)
+(CODE(MID([Start Time],2,1))>47)
+(CODE(MID([Start Time],2,1))<58)
+(CODE(MID([Start Time],4,1))>47)
+(CODE(MID([Start Time],4,1))<54)
+(CODE(MID([Start Time],5,1))>47)
+(CODE(MID([Start Time],5,1))<58)
+IF(CODE(MID([Start Time],1,1))=50, (IF(CODE(MID([Start Time],2,1))>51, 0, 1)), 1)

What this essentially does is give eleven boolean evaluable statements and adds their results. If all of them end up being true, the final boolean condition (bool + bool + bool + bool + bool + bool + bool + bool + bool + bool + bool = 11) will be met.

The first condition checks to see if the character length is five.

The second condition ensures the third character is a : character.

The third-tenth conditions convert the character to Unicode and make sure the first character is 0-2, second is 0-9, third is 0-6, and fourth is 0-9.

The eleventh condition is to make sure that the second character is only 0-3 while the first character is 2. This prevents times like 24:01 or 29:45.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.