Conditional Formatting for Dates Within 30 Days in Google Sheets

Conditional Formatting for Dates Within 30 Days in Google Sheets

To set up conditional formatting in Google Sheets for dates that are within 30 days (either in the past or the future) from today, you can follow these steps:

1. Select Your Date Range

Click and drag to select the range of cells that contain the dates you want to format. If your dates are in column A from A1 downwards, click on A1, then drag the selection down to cover all your dates.

2. Open Conditional Formatting 

Go to the "Format" menu and select "Conditional formatting". This will open the "Conditional format rules" sidebar on the right side of your screen.

3. Create a New Rule 

In the "Conditional format rules" sidebar, ensure that the range you selected is correctly displayed under the "Apply to range" section. If not, you can manually input or correct the range.

4. Set Up the Formula

Under the "Format cells if" dropdown, select "Custom formula is". In the formula box that appears, you will enter a custom formula to identify dates within 30 days of today. Use one of the following formulas depending on your needs:

For dates within the last 30 days:

=AND(A1<=TODAY(), A1>=TODAY()-30)

This formula highlights dates from the past 30 days up to today. 

For dates within the next 30 days:

=AND(A1>=TODAY(), A1<=TODAY()+30)

5. Choose Formatting Options

After entering your formula, choose the formatting style you want to apply to these cells. You can change the text color, fill color, and other formatting options under the "Formatting style" section.

6. Finalize and Apply

Once you've set your formatting options, click on "Done" at the bottom of the "Conditional format rules" sidebar. The rule will now be applied to your selected range, and any dates within 30 days (as per your formula) will be highlighted according to the formatting style you chose.

Below is what our example looks like with the custom formula =AND(A1>=TODAY(), A1<=TODAY()+30), highlighting dates that fall within the next 30 days

We hope that this article has helped you and given you a better understanding of how to apply conditional formatting for dates within 30 days in Google Sheets. If you enjoyed this article, you might also like our articles on how to use the ISBETWEEN function in Google Sheets and how to unpivot a table in Google Sheets.