Highlight Cells between Two Dates or Number

Suppose you have a list of dates on an Excel spreadsheet and you want to highlight (Format) between two given dates with a formula.

Using a similar formula you can highlight cells between two values or numbers.

Refresh your understanding of basic knowledge of Conditional Formatting.

Query: We have two columns of payment dates and item quantity. Our task is to color based on two dates.

Download Your Example Excel File

Highlight cells Greater or Less than given Dates or Number

In case you want to color based on dates like you want to format dates which have given before payment are less than or equal to (10-01-2017) with the use of Excel AND Function.

  • Open the Example File
  • Go to Conditional Formatting
  • Chose New Rule
  • Select Use a Formula to determine which cells to format.
  • Type formula   (=AND(B4>=F5,B4<=F6))
  • Click Format and choose the color
  • Click Ok
  • Again, click Ok

It will color all dates which are between the given date1 ( 01-01-2017) to date 2 (10-01-2017) in Excel.

In the same manner, we can apply this formula to color cells between numbers with conditional formatting.

Learn to calculate average or sum in a column between two dates or a range of numbers in Excel.

Note: In case you want to Count and Sum Excel Cells based on multiple colors.