Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Approximate match with multiple criteria in Excel
  • How to get address of last cell in range in Excel
  • How to use Excel OFFSET function
  • Get nth match with INDEX / MATCH in Excel
  • How to reference named range different sheet in Excel

Data Analysis Examples

  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Chart Axes in Excel
  • Subtotal function in Excel
  • Conditional Formatting Rules in Excel
  • Managing Conditional Formatting Rules in Excel

Data Validation Examples

  • Excel Data validation allow uppercase only
  • Excel Data validation date in next 30 days
  • Data validation must not exist in list
  • Excel Data validation no punctuation
  • Excel Data validation exists in list

Highlight dates between in Excel

by

This tutorial shows how to Highlight dates between in Excel using the example below;

Formula

=AND(A1>=date1,A1<=date2)
Highlight dates between in Excel

Explanation

If you want to highlight dates between two dates with conditional formatting, you can use a simple formula that uses the AND and DATE functions together. For example, if you have dates in the range B4:G11, and want to highlight cells that contain a date greater than August 1st, 2015, and less than November 1, 2015, select the range and create a new CF rule that uses this formula:

=AND(B4>=DATE(2015,8,1),B4<=DATE(2015,11,1))

Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case.

Worked Example:   Identify Duplicate Values vs Remove Duplicates in Excel

Once you save the rule, you’ll see the dates between 8/1/2015 and 11/1/2015 are highlighted. Note we are including the start and end dates by using greater than or equal to (>=) and less than or equal to (<=)

How this formula works

The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values. Because the reference to B4 is fully relative, it will update as the rule is applied to each cell in the range, and any dates that are both greater than 8/1/2015 and less than 11/1/2015 will be highlighted.

Worked Example:   How to generate random date between two dates in Excel

Use other cells for input

You don’t need to hard-code the dates into the rule. To make a more flexible rule, you can use other cells like variables in the formula. For example, you can name cell E2 “start” and cell G2 “end”, then rewrite the formula like so:

=AND(B4>=start,B4<=end)

When you change either date, the conditional formatting rule will respond instantly. By using other cells for input, and naming them as named ranges, you make the conditional formatting interactive and the formula is simpler and easier to read.

Worked Example:   Highlight cells that equal in Excel

Post navigation

Previous Post:

Highlight data by quartile in Excel

Next Post:

Highlight dates greater than in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Excel MAX, MATCH and ADDRESS function illustration
  • SEARCH, SEARCHB functions: Description, Usage, Syntax, Examples and Explanation
  • How to count specific characters in a range in Excel
  • Complete List of Excel Text Functions References and Examples
  • How to extract substring in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • TIME function: Description, Usage, Syntax, Examples and Explanation
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • Pad week numbers with zeros in Excel
  • Custom weekday abbreviation in Excel
  • Calculate loan interest in given year in Excel
  • RATE function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate present value of annuity in excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles