Currently, I have the following formula to calculate the number of weeks based on dates: =IF(IF($E$2<L16,$E$2-K16,L16-K16)/7<=1,1,IF($E$2<L16,$E$2-K16,L16-K16)/7)

I was originally having problems because I did not want the cell to equal zero. Now I need it to do just that.

If $E$2<L16,$E$2-K16,L16-K16)/7 is between 0.2 and 1, I want it to equal 1. If it is less than 0.2 I want it to equal 0. If it is greater than one, I want it to show the answer to the formula ($E$2<L16,$E$2-K16,L16-K16)/7).

Can someone help me? I don't know how to put this into the formula. <1 and >0.2?

There are a couple of problems with your question. 1 -

I want it to show the answer to the formula ($E$2<L16,$E$2-K16,L16-K16)/7).($E$2<L16,$E$2-K16,L16-K16)/7 is not a formula. Without the IF in front of it, it's just a text string.

2 - You've told us what you want if the value is

between2 numbers, you told us what you want if the value isless thanone of the numbers and you've told us what you want if the value isgreater thanthe other number.What you haven't told us is what you want if the value

equalseither of those 2 numbers.I'll assume you meant "less than or equal to" and "greater than or equal to".

I'm not going to claim that I understand what you are trying to do, but based on your question, I think this is what you want.

=IF(AND(IF(E2<L16,E2-K16,L16-K16)/7>=0.2,IF(E2<L16,E2-K16,L16-K16)/7<=1),1,IF(IF(E2<L16,E2-K16,L16-K16)/7<=0.2,0,IF(E2<L16,E2-K16,L16-K16)/7))

Amazing! It works great! Thank you, thank you, thank you!

Have a great weekend!

Glad I could help. In the future, one "Thank You" will suffice. ;-)

Ask Your Question

Weekly Poll