Forum Discussion
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).
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 Replies
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.
- Thank you so much! This was extremely helpful!!
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.
