Data Validation - Must be greater than CELL and contain : symbol

[TTUK]

Hi all,

I'm trying to get a data validation for a user input into a cell.
I cannot use the TIME formatting of the cell as it won't work for what I am trying to achieve.

I need AH15 to have a number input greater than AH14 and must contain a : symbol.

E.g. 11:20 (If AH14 will equal 11:19)

If a user enters 11.20 it will not work.

If the value is incorrect a message box to appear saying 'You have entered the format incorrectly'


Hope someone can help me with this.

Thank you!

 

[Cross Terrier]

=AND(MID(AH15,3)=":",LEFT(AH15,2)>=LEFT(AH14,2),RIGHT(AH15,2)>RIGHT(AH14,2))

This doesn't work for a time like 12:05, for example, where the hour is greater but the minutes are lower. I shall have a think about that though.

 

[Cross Terrier]

=AND(MID(AH15,3)=":",OR(LEFT(AH15,2)>LEFT(AH14,2),AND(LEFT(AH14,2)=LEFT(AH15,2),RIGHT(AH15,2)>RIGHT(AH14,2))))

This formula checks that there is both a colon in the middle of the time (and that the time is in 24hr clock), and that either the hour is greater or the minutes are greater if the hour is the same.

 

[kvsrinivasamurthy]

Formula for Data validation for AH15

=AH15>AH14

Format the range for hh:mm

 

[TTUK]

=AND(MID(AH15,3)=":",OR(LEFT(AH15,2)>LEFT(AH14,2),AND(LEFT(AH14,2)=LEFT(AH15,2),RIGHT(AH15,2)>RIGHT(AH14,2))))

This formula checks that there is both a colon in the middle of the time (and that the time is in 24hr clock), and that either the hour is greater or the minutes are greater if the hour is the same.

Hi, I believe this will do what I want it to do.
However, it says You've entered too few arguments for this function.


Thanks!