## Explanation

In the example worksheet, column D contains a date when a task is completed. If the task is not yet complete, the cell in column D will be empty (blank). In column E, the goal is to display the word "Open" when there is no date in column D. If there is a date in column D, the formula in column E should display nothing. The most common way to solve a problem like this is with the IF function.

### IF function

The IF function runs a logical test and returns one value for a TRUE result, and another value for a FALSE result. The IF function can return a hard-coded value, a cell reference, or another formula. The generic syntax for IF looks like this:

`=IF(logical_test,value_if_true,value_if_false)`

To display "Open" when there is no completed date, the formula in cell E5 is:

```
=IF(D5="","Open","")
```

The logical expression D5="" means "is empty". As the formula is copied down, it will return "Open" when the date in column D is blank because the logical test will return TRUE. If there is a value in column D, the logical test will return FALSE and IF will return an empty string ("") as a result. In Excel, an empty string will not display anything. The values returned by the IF function can be customized as needed.

### ISBLANK function

You can also use the ISBLANK function to test for an empty cell. To use ISBLANK instead of the expression D5="" revise the formula as follows:

```
=IF(ISBLANK(D5),"Open","")
```

The result is the same as the original formula above. There is no practical difference between the formulas, the choice depends on personal preference.

### LEN function

There are times when you may need to use the LEN function to check for a blank cell instead of the methods above, because *when a formula in a cell returns an empty string*, Excel will *not consider the cell blank* even though it looks blank. For example, if cell A1 contains a formula, the formulas below will always return FALSE, even if the formula returns an empty string (""):

```
=A1="" // returns FALSE
=ISBLANK(A1) // returns FALSE
```

This happens because Excel considers A1 as not blank since it contains a formula. If you need to test for values in cells that contain formulas, you can use the LEN function to check for a character count of zero:

`=LEN(A1)=0 // returns TRUE`

The LEN function will return zero if a cell is truly empty or contains an empty string generated by a formula. The expression above can be used as the logical test in the IF function

`=IF(LEN(A1)=0,"Open","")`

This is a more literal formula. We are not asking Excel if A1 is blank, we are literally counting the characters in A1. The LEN function will return a non-zero number only when a cell contains actual characters. Using the LEN function this way works for cells containing formulas as well as cells without formulas.

### Conditional formatting

Another way to highlight open tasks is to use conditional formatting. In the screen below, this formula is used to highlight rows that do not contain a completion date:

`=$D5=""`

This is an example of applying conditional formatting with a formula. When a date is entered in column D, the formatting will disappear. More examples here.