Admin CliCours
Excel advanced charts, conditional formatting, and checking formulasbéton armé, tutoriel en pdf.
Custom Conditional Formatting Using a Function
A formula entered for a custom conditional format can also make use of functions.
Learn the Skill
This exercise demonstrates how to create a custom conditional format using a function.
1 Open the Airport Departures workbook and save it as Airport Departures – (your name).
This worksheet lists the departure time for a variety of flights out of an airport.
2 Scroll down the worksheet to view the rest of the data.
You can enter a formula that will display the word “Departed” for any flight with a departure time that is earlier than the current time. The current time can be calculated by subtracting today’s date from the current date and time, which leaves only the current time value. Copy this formula down to the other rows.
3 Click in cell F2, and enter: =IF((NOW()-TODAY())>D2, »Departed », » »)
4 Copy the formula in cell F2 down to the cell range F3:F70.
Create a conditional format on column F, using the same logical test as the formula in step 3.
5 Select the cell range F2:F70.
6 On the Home tab, in the Styles group, click Conditional Formatting, then click New Rule.
7 In the New Formatting Rule dialog box, click Use a formula to determine which cells to format in the Select a Rule Type list.
8 In the Format values where this formula is true text box, type: =(NOW()-TODAY())>D2.
9 Click the Format button.
10 In the Format Cells dialog box, click the dark red standard color (bottom row, first on the left), and click OK.
11 In the New Formatting Rule dialog box, click OK to complete the creation of the conditional formatting rule.
The screen should now look similar to the following example, except the status and conditional highlighting will automatically adjust to your current time
