SOLVED

Need help with a date formula on Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-3108777%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20a%20date%20formula%20on%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3108777%22%20slang%3D%22en-US%22%3E%3CP%3EANY%20HELP%20WOULD%20BE%20GREATLY%20APPRECIATED!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20a%20date%20formula%20based%20off%20of%20the%20current%20date%20every%20time%20the%20spreadsheet%20is%20open.%3C%2FP%3E%3CP%3EInformation%20has%20to%20utilize%20a%20set%20date%20in%20Cell%20J2.%3C%2FP%3E%3CP%3EIf%20current%20date%20is%20in-between%200-180%20days%20of%20date%20in%20J2%2C%20then%20it%20is%20%22Current%22%20(Need%20this%20Cell%20to%20turn%20the%20color%20Green).%3CBR%20%2F%3EIf%20current%20date%20is%20in-between%20181-270%20days%20of%20date%20in%20J2%2C%20then%20it%20is%20%22Delinquent%22%20(Need%20this%20Cell%20to%20turn%20the%20color%20Yellow).%3CBR%20%2F%3EIf%20current%20date%20is%20in-between%20271-364%20days%20of%20date%20in%20J2%2C%20then%20it%20is%20%22Suspended%22%20(Need%20this%20Cell%20to%20turn%20the%20color%20Red).%3CBR%20%2F%3EIf%20current%20date%20is%20365%2B%20days%20of%20date%20in%20J2%2C%20then%20it%20is%20%22Revoked%22%20(Need%20this%20Cell%20to%20turn%20the%20color%20Black).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3108777%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3108956%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20date%20formula%20on%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3108956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1295574%22%20target%3D%22_blank%22%3E%40salvadominican%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20do%20that%2C%20please%20select%20the%20whole%20column%20D%20by%20clicking%20on%20the%20column%20letter%20D%20and%20make%20New%20Rules%20for%20conditional%20formatting%20using%20the%20formulas%20given%20below...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFor%20Green%20(0-180%20days)%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAND(ROW()%26gt%3B1%2CISNUMBER(J1)%2CJ1-TODAY()%26gt%3B%3D0%2CJ1-TODAY()%26lt%3B%3D180)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFor%20Yellow%20(181-270%20days)%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAND(ROW()%26gt%3B1%2CISNUMBER(J1)%2CJ1-TODAY()%26gt%3B%3D181%2CJ1-TODAY()%26lt%3B%3D270)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFor%20Red%20(271-364%20days)%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAND(ROW()%26gt%3B1%2CISNUMBER(J1)%2CJ1-TODAY()%26gt%3B%3D271%2CJ1-TODAY()%26lt%3B%3D364)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EFor%20Black%20(365%2B%20days)%3A%3C%2FSTRONG%3E%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DAND(ROW()%26gt%3B1%2CISNUMBER(J1)%2CJ1-TODAY()%26gt%3B%3D365)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20attached%20with%20the%20conditional%20formatting%20in%20place%20for%20all%20the%20colors.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3114389%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20a%20date%20formula%20on%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3114389%22%20slang%3D%22en-US%22%3EThank%20you%20so%20much!%20This%20was%20extremely%20helpful!!%3C%2FLINGO-BODY%3E
New Contributor

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

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

 

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.