To highlight dates that are coming in the next N days from today, we will use conditional formatting of Excel.
The generic formula in conditional formatting option "Use a formula to determine which cell to format"
Generic Formula
Cell: Here the cell is the relative cell reference of that contains the date.
N_Days: This is the number of days that you want to highlight from today's date. This can be hardcoded value, a cell reference or another formula that returns a number.
Let’s see an example to make things clear.
Example: Highlight dates in Next 30 days
So here I have some random dates. Now I want to highlight each date that is coming in next 30 days, including today's date and 30th date.
Select the entire range. In this case A2:A14. Goto conditional formatting and select new rules.
Choose the option "Use formula to determine which cell to format".
Now, using above generic formula, write this formula:
Goto formatting and select the formatting that you want to do for highlighting the cell. Hit OK. The formula applies to the entire range that you have selected.
Hit OK. And it is done. All the dates within the next 30 days including today's date and 30th day will be highlighted.
If you want to change the number of day's frequently, then give a reference of the cell instead of the hardoceded number and change days in that cell.
The number of days you write in the cell C2, dates in the range in the next those days will be highlighted. You conveniently check the dates and plan your routines. Using above formula you can color dates that are coming in the next N days.
How does it work?
As we know that Conditional Formatting in excel simply runs on the True False logic. So first we check the date if it is greater than or equals to today's date. Next we check if the date is less than or equal to today's date + 30 days. These two conditions are encapsulated into AND function. Hence, when both of them are True, the date is highlighted, else it is not highlighted.
So yeah guys, this is how you can highlight dates in the next 30, 40, 60 on N Days. This can help you bill your clients on time or to track your project progress.
I hope it was helpful. If you have any doubts regarding this Excel formula or you have any other excel dates related query, ask in the comments section below.
Related Articles:
Conditional formatting based on another cell value: To format cells based on the value of other cells, we just set formula on another cell while having selected the formatting cells.
IF and Conditional formatting: The IF and the condition are so interrelated. The If can also be used to conditionally format cells.
Conditional Formatting with formula 2016: The conditional formatting can be done with formulas too. To use formulas for determining the cells that you want to format.
Highlight cells that contain specific text: To highlight cells that contain a specific text, simply use the predefined rule of conditional formatting.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
