Using Stop, Skip and Resume

The SKIP, RESUME and STOP functions are powerful functions for the advanced user. When the SKIP function is used, both current and subsequent lines are skipped until the RESUME function is encountered. On the other hand, the STOP function is used to halt the execution of the formula.

How to use the Stop function

Suppose you have a formula with 6 lines, but you only want the last 2 lines to be executed if the Date column value is past. On line 4, you would write a formula checking is the Date is past and triggering the STOP function if it is not. Like this:

1:{ColumnA}=...

2:{ColumnB}=...

3:{ColumnC}=...

4:[STOP]=IF({Date}>=TODAY(),_STOP()_)

5:{ColumnD}=...

6:{ColumnE}=...

The left part of the formula ([STOP]=) has no purpose but to follow the golden rule that each line must assign a value to a variable or a column. It creates a STOP variable, which will never be used and will remain blank. It could really be anything. We could replace [STOP] with [GOOD MORNING].

Why I this useful?

It could be useful to avoid errors, like dividing a number by 0. Or to avoid certain scenarios.

Imagine that on line 5, we want to calculate the number of days between TODAY and the Date column and on line 6, calculate a cost based on the number of days. Like this:

5:[DayCount]=COUNTDAYS({Date},TODAY())

6:{Cost}=MULTIPLY([DayCount],100)

By stopping the execution on line 4 if the date is not past, we avoid getting a negative number of days which would throw us off in our cost calculation.

Another option would be to use the IF function to check if the date is past on each line, like this:

5:[DayCount]=IF({Date}>=TODAY(),COUNTDAYS({Date},TODAY()))

6:{Cost}=IF(NOTEMPTY([DayCount]),MULTIPLY([DayCount],100))

By using the STOP function, we simplify the coding of the following lines, but it is really up to you to decide what you prefer.

How to use the SKIP and RESUME functions

Whereas the STOP() function terminates the execution of the formula, the SKIP() function only makes the formula engine skip the line it is used on and the subsequent lines, until it finds the RESUME() function.

Let us examine this formula:

1:{ColumnA}=...

2:{ColumnB}=...

3:{ColumnC}=...

4:[SKIP]=IF({Date}>=TODAY(),_SKIP()_)

5:{ColumnD}=...

6:{ColumnE}=...

7:[RESUME]=_RESUME()_

8:{ColumnF}=...

Line 5 and 6 are skipped if {Date} is not past. Line 7 instructs the formula engine to stop skipping lines, so Line 8 will be executed.

In this case, there is no condition associated with the RESUME function, but we could base the resuming on a condition. For instance, we could decide to resume execution only if the date of the Date column is superior to Dec 31st, 2024.

1:{ColumnA}=...

2:{ColumnB}=...

3:{ColumnC}=...

4:[SKIP]=IF({Date}>=TODAY(),_SKIP()_)

5:{ColumnD}=...

6:{ColumnE}=...

7:[RESUME]=IF({Date}>'2024-31-12',RESUME())

8:{ColumnF}=...

If you use these 3 functions, make sure you test them using the Test pane of the Syntax Editor. It will clearly indicate to you which lines were skipped or when the execution was stopped.