Conditional Formatting to Highlight Dates Within 30 Days in Excel

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.

Dataset

Method 1

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.

Between-conditional formatting dates within 30 days

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

Inserting dates to conditional formatting within 30 days

  • You can keep the highlighting color the same. Click OK to apply the changes.

Final output

As you can see, the dates that have fallen within the month of December have been highlighted.

Method 2

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.

A date occurring-conditional formatting dates within 30 days

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

Specifying the dates to conditional formatting dates within 30 days

  • Finally, click OK to apply the changes.

Output

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

Method 3

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.

New Rule-conditional formatting dates within 30 days

  • 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))

Inserting Formula

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

Selecting Color

  • Finally, click OK to apply the changes.

output of conditional formatting dates within 30 days

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

Between Feature from Conditional Formatting

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

Inserting 90 days duration

  • You can keep the highlighting color the same. Click OK to apply the changes.

Output for 90 days

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

(Visited 48 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo