 SOLVED

New Contributor

# Need help with a date formula on Excel

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).

3 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

# Re: Need help with a date formula on Excel

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.

# Re: Need help with a date formula on Excel

Thank you so much! This was extremely helpful!!