Need help with a date formula on Excel

[salvadominican]

ANY HELP WOULD BE GREATLY APPRECIATED!!

I need a date formula based off of the current date every time the spreadsheet is open.

Information has to utilize a set date in Cell J2.

If current date is in-between 0-180 days of date in J2, then it is "Current" (Need this Cell to turn the color Green).
If current date is in-between 181-270 days of date in J2, then it is "Delinquent" (Need this Cell to turn the color Yellow).
If current date is in-between 271-364 days of date in J2, then it is "Suspended" (Need this Cell to turn the color Red).
If current date is 365+ days of date in J2, then it is "Revoked" (Need this Cell to turn the color Black).

[#2 · Accepted solution]

@salvadominican 

To do that, please select the whole column D by clicking on the column letter D and make New Rules for conditional formatting using the formulas given below...

For Green (0-180 days):

=AND(ROW()>1,ISNUMBER(J1),J1-TODAY()>=0,J1-TODAY()<=180)

For Yellow (181-270 days):

=AND(ROW()>1,ISNUMBER(J1),J1-TODAY()>=181,J1-TODAY()<=270)

For Red (271-364 days):

=AND(ROW()>1,ISNUMBER(J1),J1-TODAY()>=271,J1-TODAY()<=364)

For Black (365+ days):

=AND(ROW()>1,ISNUMBER(J1),J1-TODAY()>=365)

Please find the attached with the conditional formatting in place for all the colors.

[#3]

Thank you so much! This was extremely helpful!!

[#4]

You're welcome @salvadominican! Glad it helped.

If that resolved your issue, please take a minute to accept the post with the proposed solution as a Best Response to mark your question as Solved.