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)

---