stop calculating

[Likeexcel]

Hello:
How do I stop the formula from calculating if the date is past certain number of months?
Cell A1= 1/1/2020, Cell B1 is the current date 1/31/2022, now assume it returns zero after 12 months so in the case from 12/31/2020 on should return zero.

Below is my formula, it continues to calculate because I do not have a cap in the formula to get it to stop counting past 12 months.

=IFERROR(IF(B$1>$A1,MAX(B2-B3,0),0),0)

thank you for any suggestions!

 

[Micron]

What's in B2 and B3?

 

[Likeexcel]

B2 and B3 just sales numbers that I need to calculate the difference. thank you.

 

[Micron]

Maybe someone can figure out what you want, but when those cells are part of the formula and you don't provide data for them, as long as B > A and those values are Null, the result will always be 0 anyway? Maybe I'm wrong but being unsure, I'll wait to see what others say.

 

[jtakw]

Hi,

I'm confused also, you only have 2 cells of dates, and 2 cells for math, where are the Months or Anything being Counted?

Do you perhaps, want the result to be 0 (zero), if B1 is More than 12 Months more than A1?

If so, try this:

=IF(B$1>EDATE($A1,12),0,IF(B$1>$A1,MAX(B2-B3,0),0))

I took out the IFERROR because I don't think it's needed in this case, might be wrong.

 

[Likeexcel]

Hi,

I'm confused also, you only have 2 cells of dates, and 2 cells for math, where are the Months or Anything being Counted?

Do you perhaps, want the result to be 0 (zero), if B1 is More than 12 Months more than A1?

If so, try this:

=IF(B$1>EDATE($A1,12),0,IF(B$1>$A1,MAX(B2-B3,0),0))

I took out the IFERROR because I don't think it's needed in this case, might be wrong.

jtakw: This works! Thank you so much!

 

[jtakw]

jtakw: This works! Thank you so much!

You're welcome.

 

[Micron]

@jtakw;
Question if you please:
If the date comparision has already been made and B > A, is there a need to test for it again?
What about

IF(B$1>EDATE($A1,12),0,MAX(B2-B3,0))

 

[jtakw]

@jtakw;
Question if you please:
If the date comparision has already been made and B > A, is there a need to test for it again?
What about

IF(B$1>EDATE($A1,12),0,MAX(B2-B3,0))

Yes, it needs to be tested again, in case B1 is Less (older) than A1, I assumed that might happen since OP was testing the dates in his original formula.

 

[Likeexcel]

Yes, i

Yes, it needs to be tested again, in case B1 is Less (older) than A1, I assumed that might happen since OP was testing the dates in his original formula.

No, A1 is older than B1, B1 is the current date and A1 is the starting date. thank you.
t needs to be tested again, in case B1 is Less (older) than A1, I assumed that might happen since OP was testing the dates in his original formula.