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
- 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.