Feb 02 2022 07:06 PM - edited Feb 02 2022 07:31 PM
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).
Feb 02 2022 08:32 PM
Solution
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.
Feb 03 2022 03:50 PM
Feb 03 2022 07:02 PM
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.
Feb 02 2022 08:32 PM
Solution
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.