[Solved] advancing month and year by 12

[ranjit]

How to write a formaula, for example, if 3/15/23 is greater than 12 months change the particular cell to 3/15/24 and highlight this cell in Red.

Thank you somuch for the help!

Ranjit

 

[Rubayed Razib]

Greetings ranjit,
Thanks a lot for your query. For this problem, you can use the conditional formatting to resolve this issue.
In this problem, you asked to check whether a date is greated than 12 months or not. But it is not clear whether you asked for checking whether the date is greater than todays date. Assuming this, we presented the below solution.
First to get new date, enter the following formula in the cell C4,
=IF(DATEDIF(B4,TODAY(),"m")>12,DATE(YEAR(B4)+1,MONTH(B4),DAY(B4)),"")

if the date in the cell B4 is greater than 12 months, then the year value will increased by 1 in the cell C4.
Now we can implement the conditional formatting in the cell to turn them in red if conditions met properly.
GO to Home>Conditional Formatting>New Rules.

In the edit formatting rule window, go to Use a formula where this formula is true.
And in the formula box enter the following formula:
=DATEDIF(B4,TODAY(),"m")>12
And in the Format option , set the format to red background.
click OK after this.

Now you can see that the cell C4 background is now set to red.