SOLVED

Formula return black if answer is 0

%3CLINGO-SUB%20id%3D%22lingo-sub-2278859%22%20slang%3D%22en-US%22%3EFormula%20return%20black%20if%20answer%20is%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278859%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20All%2C%20I%20am%20working%20with%20a%20lease%20agreement%20and%20want%20the%20formula%20to%20stop%20calculating%20as%20soon%20as%20the%20amount%20gets%20to%20zero%20and%20then%20leave%20it%20as%20blank%20cells%20onwards.%20The%20date%20should%20also%20return%20as%20black%20as%20soon%20as%20the%20amount%20gets%20to%20zero%2C%20If%20possible%3F%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Janedb_0-1618915103891.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F274022iCFE23745F3916180%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Janedb_0-1618915103891.png%22%20alt%3D%22Janedb_0-1618915103891.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2278859%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282201%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20return%20black%20if%20answer%20is%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282201%22%20slang%3D%22en-US%22%3EAs%20always....you%20are%20a%20star.%20Thnx!!!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281380%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20return%20black%20if%20answer%20is%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281380%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20A35%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIF(B35%3D%22%22%2C%22%22%2CDATE(YEAR(A34)%2CMONTH(A34)%2B1%2C1))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EFill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2281374%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20return%20black%20if%20answer%20is%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2281374%22%20slang%3D%22en-US%22%3EThnx%20Hans%2C%20is%20there%20a%20way%20to%20do%20the%20same%20with%20the%20date%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2279900%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20return%20black%20if%20answer%20is%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279900%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks!%20See%20the%20attached%20version.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2279877%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20return%20black%20if%20answer%20is%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2279877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3BSample%20spreadsheet%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2278879%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20return%20black%20if%20answer%20is%200%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2278879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509992%22%20target%3D%22_blank%22%3E%40Janedb%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20provide%20some%20information%20about%20the%20formulas%20involved%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

HI All, I am working with a lease agreement and want the formula to stop calculating as soon as the amount gets to zero and then leave it as blank cells onwards. The date should also return as black as soon as the amount gets to zero, If possible?

Janedb_0-1618915103891.png

 

 

 

 

6 Replies

@Janedb 

Could you provide some information about the formulas involved?

@Hans Vogelaar Sample spreadsheet attached.

@Janedb 

Thanks! See the attached version.

Thnx Hans, is there a way to do the same with the date?
best response confirmed by Janedb (Contributor)
Solution

@Janedb 

In A35:

=IF(B35="","",DATE(YEAR(A34),MONTH(A34)+1,1))

Fill down.

As always....you are a star. Thnx!!!