Contents
Through this article, we are going to tell you how to do conditional formatting of dates more than 30, 45, 60, and 90 days from today. We are giving you a tutorial for it which gonna help you out. If someone wants to know how to do conditional formatting for more than 120 days, etc, you will find a solution for it at the end of this article.
So, don’t forget to read the article till the end otherwise you will gonna miss something.
Here we go…
First of all, let’s understand what conditional formatting is.
You can easily highlight specific values or make selected cells stand out with conditional formatting. This modifies a cell range’s appearance according to a criterion (or criteria). To highlight cells that contain values that satisfy a specific requirement, use conditional formatting.
Additionally, formatting a complete range of cells allows you to change the format as each cell’s value changes.
Formatting dates that are 30, 45, 60 and 90 days from today conditionally can be done in many ways. The method listed below is what we consider to be the simplest. We use Excel’s TODAY function rather than “hard-coding” the current date for these conditional formatting rules to execute regardless of when we access the worksheet.
For the four time periods, we will develop four conditional formatting rules. Note that the rule formulae are in the top left cell of your cell range, as illustrated in the worksheet below (ours is C4). Additionally, pick your colors in advance.
How to do conditional formatting of dates 30, 45, 60, and 90 days from today?
These are the following steps to do conditional formatting of dates 30, 45, 60, and 90 days from today. You have to follow the steps strictly in sequence.
Step 1: For conditional formatting, first choose the cells that have dates in them.
Step 2:-Select New Rule from the drop-down menu that appears when you select Conditional Formatting from the Styles area of the Excel ribbon.
Step 3: Choose Use a formula to select which cells to format from the New Formatting Rules dialogue box.
Step 4:-Change C4 to the top left cell of YOUR range and enter the formula =C4<TODAY()-30 in the blank formula section.
Step 5:-Select the Format button. In the Format Cells dialogue box, select your preferred color(s), then click OK. A preview appears in the dialogue box. Repeatedly click OK.
The formatting of all dates more than 30 days from today is now complete, but we’re not done yet! Your cell range should still be selective. If not, choose again.
Step 6:-You have to repeat the steps from step 2 to step 5 for more than 45 days from today, replacing C4 with YOUR cell reference and using the formula =C4<TODAY()-45.
Step 7:- Similarly, follow the above steps from step 2 to step 5 with this formula =C4<TODAY()-60, replacing C4 with YOUR cell reference, for a while greater than 60 days from today while keeping the cell range selected.
Step 8:- If one wants to format for more than 90 days from today, you can simply go through the steps from step 2 to step 5 using the formula =C4<TODAY()-90, replacing C4 with your cell reference and keeping the cell range specified.
Now the conditional formatting of dates more than 30, 45, 60, and 90 days from today completed. At the top of your worksheet, be sure to indicate the color legend.
Repetition of the preceding procedures using 120 as the formula will format dates greater than 120. With different sets of numbers, repeat these processes and modify the formulas. Utilize the formula =C4<TODAY()-90, for instance, to format dates greater than 90 days from today.
Above used Formula explanation
Excel keeps each date as a serial number so that we may compare dates or use them in computations, regardless of how a date is to appear in a spreadsheet. Serial numbers increase in size; for example, the date serial number for today is one greater than the serial number for yesterday.
The equation is =C4<TODAY()-30. compares each date (beginning with C4) to the current date minus thirty. 09/08/21 is the current date (8-Sep-21). 30 days from today is equal to August 8, 21 (9-Aug-21).
This conditional formatting rule is broken by the date 08/10/21 (10-Aug-21) in cell C10. However, the date in cell C8, 07/08/21 (8-Jul-21), satisfies the requirement and is formatted in accordance with our chosen color scheme.
Conclusion
In the end, we hope this tutorial will seem to be beneficial for you. We had tried to use easy and understandable language so that when you try out this tutorial, you will not face any difficulties. Still, if any difficulty arises while performing this tutorial, you can simply comment in the comment section, we will provide you with the solution as soon as possible.
If you’re not satisfied with the rules you’ve chosen for the cell range, you may remove them by selecting the Conditional Formatting button on the ribbon, Clear Rules, and Clear Rules from Selected Cells.
For more: Is Celine Dion Weight Loss True?
About The Author
