Forum Discussion
IF-function for date in excel, some dates becomes 0
Hi Akila,
Some dates are returning 0 because the first part of your formula IF(B13=420,C13+$O$6,IF(B13=500,C13+$O$7)) is restricted to only two values. According to your workbook, you have four values in Column B (500,420,150 and 320) but your formula is only referencing 420 and 500. So when it gets to a row that has either 150 or 320, the first part of the formula will return 0 which is why some of the dates becomes 0.
The same thing also for the second part of your formula (IF(B6=150,(C6+$O$8)*(IF(B6=320,(C6+$O$9),0))))); it is only referencing two values.
I will advise that you adjust your formula to cover the four values in Column B or use a a combination of IF and OR Function.
I hope this helps.
I solved it myself! I needed to use + between the arguments in the formula! Thanks again for your reply and the help to solving my issue!!! 🙂
Thank you so much for your answer, and quick reply!
So basically, what I have done is that I have divided the formula into two parts?I will try to figure it out on my own, but if you have time to help, how do I fix the problem without having to insert a second formula like OR? I think that is too advanced for me.
Regards!
Hello Akila,
You can use the IFS function to fix the problem - see below.
=IFS(B6=420,C6+$O$6,B6=500,C6+$O$7,B6=150,C6+$O$8,B6=320,C6+$O$9)
This should work fine and you do not need the second part of the formula again.
I hope this helps.
