Data Validation

[liampog]

Hi there

I'm trying to validate data entered by the user that should be entered into a cell which is pre-defined as being "Text" format in the following way:

hh:mm - hh:mm

Is there any way I can do this as a custom Data Validation using a formula?

I tried using:

=AND(LEFT(F21,1)=OR("0","1","2"),LEFT(F21,2)=OR("0","1","2","3","4","5","6","7","8","9"))

and so on for each of the 13 characters required, but there must be a length limit to the formula because it got to the point where I couldn't type any further.

Thanks in advance for any ideas.

Liam

 

[Haseeb Avarakkan]

Hello, Try

=AND(MID(F21,1,5)+0<=RIGHT(F21,5)+0,RIGHT(F21,5)+0<="23:59"+0,MID(F21,6,3)=" - ",LEN(F21)=13)

 

[liampog]

Hi there

Thanks for the speedy reply.

I had a look at the MID command and understand how it works so I had to adapt your answer.

The time on the left can actually be greater than the time on the right as the user is entering shifts, some of which are night shifts.

I tweaked it a little bit and came up with the following, which works perfectly:

=AND(MID(F21,1,2)<="23",MID(F21,3,1)=":",MID(F21,4,2)<="59",MID(F21,6,3)=" - ",MID(F21,9,2)<="23",MID(F21,11,1)=":",MID(F21,12,2)<="59",LEN(F21)=13)

I had to specify the hours and minutes upper limits because otherwise it would allow times like 20:70, etc.

Thanks for your help though! Without your reply, I wouldn't have been able to work it out.

Liam