June 11

0 comments

Using RAG statuses with dates in Microsoft Excel

[fusion_builder_container hundred_percent=”no” hundred_percent_height=”no” hundred_percent_height_scroll=”no” hundred_percent_height_center_content=”yes” equal_height_columns=”no” menu_anchor=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” status=”published” publish_date=”” class=”” id=”” background_color=”” background_image=”” background_position=”center center” background_repeat=”no-repeat” fade=”no” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ video_mp4=”” video_webm=”” video_ogv=”” video_url=”” video_aspect_ratio=”16:9″ video_loop=”yes” video_mute=”yes” video_preview_image=”” border_size=”” border_color=”” border_style=”solid” margin_top=”” margin_bottom=”” padding_top=”” padding_right=”” padding_bottom=”” padding_left=””][fusion_builder_row][fusion_builder_column type=”1_2″ layout=”1_2″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_image_id=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

The problem

I recently had a plea for help from a client for whom I was running an Excel training course.

They wanted to highlight dates an Excel worksheet in various ways – red for dates in the past, amber for dates in the next week, yellow for dates a couple of weeks away, and green for dates further away than that.

This is an issue that will be familiar to anyone who deals with deadlines: orders due for shipping, service contracts due for renewal, milestones hurtling towards you in project management or exam dates if you’re a student.

The solution

Any time you want to highlight values in an Excel Worksheet based on criteria you set, you’ll be using the same tool – Conditional Formatting. You can find it on the Home tab of the Ribbon, about two-thirds of the way along:

Conditional formatting button in Excel

[/fusion_text][/fusion_builder_column][fusion_builder_column type=”1_2″ layout=”1_2″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_image_id=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_builder_row_inner][fusion_builder_column_inner type=”1_2″ layout=”1_2″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” dimension_margin=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_imageframe image_id=”6024|full” max_width=”” style_type=”” blur=”” stylecolor=”” hover_type=”none” bordersize=”” bordercolor=”” borderradius=”” align=”none” lightbox=”no” gallery_id=”” lightbox_image=”” lightbox_image_id=”” alt=”” link=”https://theitservice.co.uk/wp-content/uploads/2019/06/Conditional-Format-Dates.xlsx” linktarget=”_self” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=””]https://theitservice.co.uk/wp-content/uploads/2019/06/Excel-logo.png[/fusion_imageframe][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Click to download the sample Excel file

[/fusion_text][/fusion_builder_column_inner][fusion_builder_column_inner type=”1_2″ layout=”1_2″ spacing=”” center_content=”no” hover_type=”none” link=”” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_position=”left top” background_repeat=”no-repeat” border_size=”0″ border_color=”” border_style=”solid” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” dimension_margin=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no” border_position=”all”][fusion_imageframe image_id=”6023|full” max_width=”” style_type=”” blur=”” stylecolor=”” hover_type=”none” bordersize=”” bordercolor=”” borderradius=”” align=”none” lightbox=”no” gallery_id=”” lightbox_image=”” lightbox_image_id=”” alt=”” link=”https://theitservice.co.uk/wp-content/uploads/2019/06/Conditionally-format-dates-in-Excel.pdf” linktarget=”_self” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=””]https://theitservice.co.uk/wp-content/uploads/2019/06/Word-logo.png[/fusion_imageframe][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Click to download this guide – with bonus additional content!

[/fusion_text][/fusion_builder_column_inner][/fusion_builder_row_inner][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container][fusion_builder_container hundred_percent=”no” hundred_percent_height=”no” hundred_percent_height_scroll=”no” hundred_percent_height_center_content=”yes” equal_height_columns=”no” menu_anchor=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” status=”published” publish_date=”” class=”” id=”” background_color=”” background_image=”” background_position=”center center” background_repeat=”no-repeat” fade=”no” background_parallax=”none” enable_mobile=”no” parallax_speed=”0.3″ video_mp4=”” video_webm=”” video_ogv=”” video_url=”” video_aspect_ratio=”16:9″ video_loop=”yes” video_mute=”yes” video_preview_image=”” border_size=”” border_color=”” border_style=”solid” margin_top=”” margin_bottom=”” padding_top=”” padding_right=”” padding_bottom=”” padding_left=””][fusion_builder_row][fusion_builder_column type=”1_1″ layout=”1_1″ spacing=”” center_content=”no” link=”” target=”_self” min_height=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=”” background_color=”” background_image=”” background_image_id=”” background_position=”left top” background_repeat=”no-repeat” hover_type=”none” border_size=”0″ border_color=”” border_style=”solid” border_position=”all” padding_top=”” padding_right=”” padding_bottom=”” padding_left=”” margin_top=”” margin_bottom=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=”” last=”no”][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

There are a number of ways you can approach this task – so on this page are just a few examples of how you can conditionally format dates in Excel.

To follow these examples refer to the workbook Conditional Format Dates.xlsx

Example 1. A simple way to highlight dates before or after today’s date

To conditionally format selected cells, use Excel’s built in Highlight Cell Rules with the =TODAY() function.

Spot dates in the future:

  1. Select the range of cells containing the dates you want to format
  2. On the Home ribbon click Highlight Cell Rules à Greater Than
  3. In the Greater than box enter =today()
  4. Optionally click the drop-down box ‘with’ and choose a different format
  5. Click OK

[/fusion_text][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Spot dates in the past:

  1. Select the range of cells containing the dates you want to format
  2. On the Home ribbon click Highlight Cell Rules à Less Than
  3. In the Less than box enter =today()
  4. Optionally click the drop-down box ‘with’ and choose a different format
  5. Click OK

Note: The Excel TODAY function returns the current date, updated continuously when a worksheet is changed or opened. When you open the workbook in the future, the conditional formatting automatically updates. The screenshots in this example were taken on 8 June 2019 and you will get different results to those shown.

[/fusion_text][fusion_content_boxes layout=”clean-vertical” columns=”2″ title_size=”” heading_size=”2″ title_color=”” body_color=”#03a9f4″ backgroundcolor=”” icon=”” iconflip=”” iconrotate=”” iconspin=”no” iconcolor=”” icon_circle=”” icon_circle_radius=”” circlecolor=”” circlebordersize=”” circlebordercolor=”” outercirclebordersize=”” outercirclebordercolor=”” icon_size=”40″ icon_hover_type=”” hover_accent_color=”” image=”” image_id=”” image_width=”” image_height=”” link_type=”” button_span=”” link_area=”” link_target=”” icon_align=”left” animation_type=”” animation_delay=”” animation_offset=”” animation_direction=”left” animation_speed=”0.3″ margin_top=”” margin_bottom=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””][fusion_content_box title=”Tip” backgroundcolor=”rgba(255,152,0,0.4)” icon=”fa-info fas” iconflip=”” iconrotate=”” iconspin=”no” iconcolor=”” circlecolor=”” circlebordersize=”” circlebordercolor=”” outercirclebordersize=”” outercirclebordercolor=”” image=”” image_id=”” image_width=”” image_height=”” link=”” linktext=”Read More” link_target=”” animation_type=”” animation_direction=”left” animation_speed=”0.3″ animation_offset=””]

To clear existing conditional formatting from a selected range of cells, choose Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells

[/fusion_content_box][/fusion_content_boxes][fusion_text columns=”2″ column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Example 2. A simple way to highlight dates using 10 built in options:

Excel provides 10 options to format selected cells based on the current date.

  1. Select the range of cells containing the dates you want to format
  2. On the Home ribbon click Highlight Cell Rules à A Date Occurring
  3. Select one of the date options from the drop-down list in the left-hand part of the window, ranging from last month to next month.
  4. Choose one of the pre-defined formats or set up your custom format by choosing different options on the Font, Border and Fill tabs, then click OK

[/fusion_text][fusion_text columns=”1″ column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

Example 3. A simple way to highlight upcoming dates:

This example formats dates occurring in the next 14 days orange and also formats dates occurring in the next 30 days a yellow:

  1. Select the range of cells containing the dates you want to format
  2. On the Home ribbon click Highlight Cell Rules à Between
  3. In the first box type the formula =today()
  4. In the second box type the formula =today()+14
  5. Click the drop-down arrow in the with box and choose a custom format, then choose a fill colour of orange and click OK.
  6. Repeat the steps for the second date range: on the Home ribbon click Highlight Cell Rules à Between
  7. In the first box type the formula =today()+15
  8. In the second box type the formula =today()+30
  9. Click the drop-down arrow in the with box and choose a custom format, then choose a fill colour of yellow and click OK.

[/fusion_text][fusion_text columns=”” column_min_width=”” column_spacing=”” rule_style=”default” rule_size=”” rule_color=”” hide_on_mobile=”small-visibility,medium-visibility,large-visibility” class=”” id=””]

For more examples of how to use conditional formatting to highlight dates – including formatting an entire row – download the full guide as a PDF.

[/fusion_text][/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]


Tags

conditional formatting, dates, excel


You may also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Direct Your Visitors to a Clear Action at the Bottom of the Page