One of the most popular and widely used features of Excel is Conditional formatting. Conditional formatting lets users put conditions on the content of a cell. If the content of the cell meets the criteria of the condition, the user-defined outlook gets implemented. Today in this article, we will show you how to highlight conditional formatting for dates within 30 days in Excel, meaning we will work under the condition that will meet the specified dates.
๐ย Download Excel File
Download this file to practice.
Learn to Highlight Dates Using Conditional Formatting Within 30 Days in Excel with These 3 Methods
In this article, we will learn 3 effective methods for highlighting dates with conditional formatting. We will use a dataset of a bunch of fruits and their delivery dates and highlight certain dates for delivery within 30 days or on a specific date. We will also showcase how users can form a formula with AND and TODAY functions on their own.
1. Formatting Within a Range of Dates
As the title suggests, we will use Excelโs own built-in feature, Conditional Formatting, to highlight dates. In this method, we will highlight a range of dates where delivery has been completed within a month.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- First, select the cells where you want to put the condition. We selected C6:C15.
- Then go to the Home tab and in the Styles group, select Conditional Formatting. Then from the menu, select Highlight Cell Rules. And finally, select Between.
- The Between dialogue box will open up demanding two dates. The first date will be the starting date of our range and the next one will be the last. Since we want to highlight the delivery done in the month of December 2022, we will select December 1 as our first date and December 31 will be our last date.
- You can keep the highlighting color the same. Click OK to apply the changes.
As you can see, the dates that have fallen within the month of December have been highlighted.
2. Formatting for Specific Dates
Another option found in the list of Highlighting Cell Rules is the A Date Occurring. With this option, you have the choice to put Conditional Formatting on a specific date or month, like last month, this month, or the next month. In this case, we will highlight the dates where delivery has been completed this month.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- First, select the cells where you want to put the condition. We selected C6:C15.
- Then go to the Home tab and in the Styles group, select Conditional Formatting. Then from the menu, select Highlight Cell Rules. And finally, select A Date Occurring.
- Subsequently, A Date Occurring dialogue box will appear. We have the option to choose dates. We will select This month. You can choose according to your preference. Also, keep the color as it is.
- Finally, click OK to apply the changes.
As you can see, the dates that have fallen within the month of January have been highlighted.
๐ Read More: 4 Ways to Use Conditional Formatting for Dates Overdue in Excel
3. Using AND and TODAY Functions
If the above built-in formulas donโt work out for you, you can also put in a formula of your own. In this method, we will show you how to use custom functions with the AND and TODAY functions. The AND function is used when more than one condition is required at the same time. The TODAY function generates the present date.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- First, select the cells where you want to put the condition. We selected C6:C15.
- Then go to the Home tab and in the Styles group, select Conditional Formatting. Then from the menu, instead of selecting Highlight Cell Rules, we will select New Rule.
- This selection will launch the Edit Formatting Rule dialog box. From the Select a Rule Type bar, choose Use a formula to determine which cells to format.
- Then enter the following formula in the Edit the Rule Description Then to choose a Fill color, click Format.
=AND(E6<=TODAY(),E6>=(TODAY()-30))
๐จ Formula Breakdown
๐
In the first segment of the formula (E6<=TODAY()), the TODAY function will determine whether or not the date in Cell E6 is less than or equal to the current date in this case. It, therefore, returns TRUE.
๐
The next step (E6>=(TODAY()-30) is to determine if the date is greater than or equal to todayโs date minus 30 days, at which point TRUE is returned.
๐
Last but not least, the AND function combined these two conditions. The function will highlight the dates when both of them are true; otherwise, it is not.
- Now select the Fill tab and the color you want. Pressing OK will take you back to the Edit Formatting Rule.
- Finally, click OK to apply the changes.
๐ Read More: Conditional Formatting Based on Another Cell Date in Excel
Conditional Formatting Dates Within 90 days in Excel
As the title suggests, we will use Excelโs own built-in feature, Conditional Formatting, to highlight dates. In this method, we will highlight a range of dates where delivery has been completed within 90 days.
โฌ๏ธโฌ๏ธ STEPS โฌ๏ธโฌ๏ธ
- First, select the cells where you want to put the condition. We selected C6:C15.
- Then go to the Home tab and in the Styles group, select Conditional Formatting. Then from the menu, select Highlight Cell Rules. And finally, select Between.
- The Between dialogue box will open up demanding two dates. The first date will be the starting date of our range and the next one will be the last. Since we want to highlight the delivery done between the months of October and December 2022, we will select October 1 as our first date and December 31 will be our last date.
- You can keep the highlighting color the same. Click OK to apply the changes.
As you can see, the dates that have fallen within the months of October and December 2022 have been highlighted.
๐ Read More: Conditional Formatting Dates Older Than Today in Excel
๐ Takeaways from This Article
๐๏ธ
First, we used the Between Feature from Conditional Formatting to highlight dates within specified dates.
๐๏ธ
Next, we demonstrated the A Date Occurring feature highlighting dates within certain dates.
๐๏ธ
Finally, to formulate a custom feature we used two functions.
Conclusion
We have come to the conclusion of our article. I hope that after reading this article, you can now highlight certain dates within 30 days using Conditional Formatting in Excel.ย If you have any questions, feel free to comment below.ย I will try to answer them as soon as possible. Have fun using Excel, and you can visit Excelden.com for more tutorials. Thank You.
Related Articles
- Excel VBA: If Font Color Is Red Then Return Specific Output
- How to Apply Excel Formula to Change Text Color Based on Value
- 5 Easy Ways to Highlight Highest Value in Excel
- 6 Easy Ways to Highlight Lowest Value in Excel