# 10. Exercise - Date functions

## Prerequisites

You should read the Excel Functions course up to the chapter on Date Functions before starting this exercise. You should also review the chapter on Conditional Formatting.

## Question

Consider the following worksheet extract where "Dates of birth" is entered in column B.

Use Excel's conditional formatting technique to show people over the age of 24:

- on the current date
- on a fixed date, for example 10/03/2021.

## Indications Exercise – Date functions

### 1 - People over 24 years old on the current date

To apply conditional formatting to the cell range A2:B20, select this range and click on the **Conditional Formatting** command on the **Home** tab of the Ribbon. Choose **New Rule...**

In the **New Formatting Rule** dialog box that appears:

- Click
**Use a formula to determine which Cells to format**from the list**Select a Rule Type...** - Then click on the
**Format...**button to open the**Format Cells**dialog box and choose the appropriate format. The use of this dialog box is mentioned in section 7.3 in the Conditional Formatting chapter. - In the
**Format values where this formula is true**field enter the formula :

**=EDATE($B2,288)<TODAY()**

*Explanation :*

The formula to get the date when the person has reached or will reach 24 years is :

EDATE($B2;288)

The 2^{nd} argument is 24*12 or 288.

The date when the person has reached or will reach 24 years of age is compared with the current date which is the result of the Excel function TODAY.

### 2 - People over 24 years old on date "10/03/2021"

*Solution :*

The formula to use in the **Format values where this formula is true** field is :

**=EDATE($B2,288)<DATE(2021,3,10)**

*Explanation :*

The formula is similar to that of the 1^{st} question, except that the DATE function is used here to obtain the date 10/03/2021.

NB. Excel will not recognize the date if you write "10/03/2021". This is why I used the DATE function. However, you can use the serial number corresponding to the date 10/03/2021, i.e. 44265. The following formula is correct:

=EDATE($B2;288)<44265

I remind you that to find this number :

- Write in a cell 10/03/2021
- Use the
**Format**button in the**Cells**group of the**Home**tab of the Ribbon and click on**Format Cells...**in the menu that appears - The
**Format Cells**dialog box appears. Click on the**Number**tab. Finally click on**General**in the**Category**list. Validate with the**OK**button.

NB. The use of the function EDATE is not necessary. It is sufficient to compare the date of birth with the date 10/03/1997. The following formula is also correct:

=$B2<DATE(1997;3;10)