Excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1559763%22%20slang%3D%22en-US%22%3EExcel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1559763%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20is%20the%20best%20formula%20for%20the%20following%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ecolumn%20a%20is%20a%20program%20date%3C%2FP%3E%3CP%3Ecolumn%20b%20is%20Yes%20or%20No%3C%2FP%3E%3CP%3Ecolumn%20c%20is%20todays%20day%3C%2FP%3E%3CP%3Ecolumn%20d%20is%20days%20out%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20formula%20will%20give%20me%20the%20following%20results%20in%20column%20d.%20%26nbsp%3BIf%20Column%20c%20minus%20column%20a%20is%20%26lt%3B%200%20and%20Column%20b%20is%20Yes%20or%20No%20then%20result%20is%200%2C%20%26nbsp%3BIf%20column%20c%20minus%20column%20a%20is%20%26gt%3B0%20And%20column%20b%20is%20No%20then%20show%20result%20of%20calculation%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1560252%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1560252%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F746463%22%20target%3D%22_blank%22%3E%40Dazlin1027%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20already%20have%20the%20logic%20in%20your%20text.%20It%20is%20only%20a%20matter%20of%20putting%20it%20together.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rolf-42_0-1596281641711.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F209536i0EBB8CDCE8936089%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rolf-42_0-1596281641711.png%22%20alt%3D%22Rolf-42_0-1596281641711.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3DIF(C2-A2%26lt%3B0%2C0%2C(IF(AND(C2-A2%26gt%3B0%2CB2%3D%22no%22)%2CC2-A2%2C0)))%3C%2FP%3E%3CP%3EYou%20can%20even%20drop%20the%20column%20C%20and%20have%20today()%20instead%20in%20your%20formula%20like%20this%26nbsp%3B%3DIF(TODAY()-A2%26lt%3B0%2C0%2C(IF(AND(TODAY()-A2%26gt%3B0%2CB2%3D%22no%22)%2CTODAY()-A2%2C0)))%3C%2FP%3E%3CP%3EI%20hope%20it%20helps%3C%2FP%3E%3CP%3ECheers%3CBR%20%2F%3ERolf%3CBR%20%2F%3E%3CBR%20%2F%3E%23MCT%20%23LearnWithRolf%20%23TheCloud42%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

What is the best formula for the following

 

column a is a program date

column b is Yes or No

column c is todays day

column d is days out

 

What formula will give me the following results in column d.  If Column c minus column a is < 0 and Column b is Yes or No then result is 0,  If column c minus column a is >0 And column b is No then show result of calculation

1 Reply

Hello @Dazlin1027

 

You already have the logic in your text. It is only a matter of putting it together.

Rolf-42_0-1596281641711.png

=IF(C2-A2<0,0,(IF(AND(C2-A2>0,B2="no"),C2-A2,0)))

You can even drop the column C and have today() instead in your formula like this =IF(TODAY()-A2<0,0,(IF(AND(TODAY()-A2>0,B2="no"),TODAY()-A2,0)))

I hope it helps

Cheers
Rolf
#MCT #LearnWithRolf #TheCloud42