Skip to main content

Validation Rule: Invalid when is lesser than/greater than [...]

#EN #Excel #Validation

Updated over a week ago

What is the validation rule to use?

Validation Rules

Description/Usage

Invalid when lesser than {value}
​or Invalid when < than {value}

Will be invalid when the cell value is lesser than a specified value

Invalid when lesser than or equal to {value}

or Invalid when <= {value}

or Invalid when ≤ {value}

Will be invalid when the cell value is lesser than or equal to a specified value

Invalid when lesser than {field}
​or Invalid when < than {field}

Will be invalid when the cell value is lesser than a specified field/column's value

Invalid when lesser than or equal to {field}

or Invalid when <= {field}

or Invalid when ≤ {field}

Will be invalid when the cell value is lesser than or equal to a specified field/column's value

Invalid when greater than {value}
​or Invalid when > than {value}

Will be invalid when the cell value is greater than a specified value

Invalid when greater than or equal to {value}

or Invalid when >= {value}

or Invalid when ≥ {value}

Will be invalid when the cell value is greater than or equal to a specified value

Invalid when greater than {field}
​or Invalid when > than {field}

Will be invalid when the cell value is greater than a specified field/column's value

Invalid when greater than or equal to {field}

or Invalid when >= {field}

or Invalid when ≥ {field}

Will be invalid when the cell value is greater than or equal to a specified field/column's value

With a timespan

Description/Usage

Invalid when lesser than {DD.HH:MM}

or Invalid when < {DD.HH:MM}

Will be invalid when the timespan result is lesser than a specified timespan limit in days, hours and/or minutes

Invalid when lesser than or equal to {DD.HH:MM}

or Invalid when <= {DD.HH:MM}

or Invalid when ≤ {DD.HH:MM}

Will be invalid when the timespan result is lesser than or equal to a specified timespan limit in days, hours and/or minutes

Invalid when greater than {DD.HH:MM}
​or Invalid when > than {DD.HH:MM}

Will be invalid when the timespan result is greater than a specified timespan limit in days, hours and/or minutes

Invalid when greater than or equal to {DD.HH:MM}

or Invalid when >= {DD.HH:MM}

or Invalid when ≥ {DD.HH:MM}

Will be invalid when the timespan result is greater than or equal to a specified timespan limit in days, hours and/or minutes

Why use this validation rule?

With this validation rule, you will be able to:

  • Set an invalid bar (red bar) on a field/column filled with a specific value compared with another value or field/column's value

  • A numeric field/column can be compare with up to 7 digits, regardless of decimals

  • A text field/column can be compared with up to 15 digits, regardless of decimals, as long as there are no text or special character in the cell

  • A timespan field/column can be compared using the DD.HH:MM format

    • DD = Days; HH = Hours; MM = Minutes

    • You need to at least specify the number of hours and/or minutes, but the number of days is optional (i.e.: 2.00:00 = 2 days; 02:00 = 2 hours; 00:20 = 20 minutes)

    • 59 is the limit of minutes, which turns into an hour if higher

    • 23 is the limit of hours, which turns into a day if higher

    • There is no limit of days

  • This validation rule need to be the last one you input

  • You can have multiple Invalid when lesser than [...] or Invalid when greater than [...] validation rules, but be cautious not to contradict your programming

How to use this validation rule?

As a FIELD

Using columns A, B and C in the Excel spreadsheet, you can create a field.

  • Column A will be used for the TITLE

    • The title will be displayed to add a description regarding your input

  • Column B will be used for the KEYWORD

    • The keyword defines what the field will be used for

  • Column C will be used for the VALIDATION RULE(S)

Here is an example:

Here is how it displays in the application:

As a COLUMN

Using three consecutive rows (or as we refer to it, 123) in the Excel spreadsheet, you can create a column.

  • Row 1 will be used for the TITLE

    • The title will be displayed to add a description regarding your input

  • Row 2 will be used for the KEYWORD

    • The keyword defines what the column will be used for

  • Row 3 will be used for the VALIDATION RULE(S)

Here is an example:

Here is how it displays in the application:

Available keywords for this validation rule

Did this answer your question?