Forum Discussion
IF-function for date in excel, some dates becomes 0
Hi!
I am doing a payable in excel for an assignment. I'm using the IF-function to calculate what the date should be in the column named date of payment.
This is the function I have created: =OM(B6=420;C6+$O$6;OM(B6=500;C6+$O$7)*(OM(B6=150;(C6+$O$8)*(OM(B6=320;(C6+$O$9);0)))))
When I double-click in the right bottom corner to make the function go all the way down in the spreadsheet for as many rows I am using, some dates is correct and some are not.
For example: in one column it says 2020-05-30 and if the invoice came to the account 420 it should add 30 days to the dates and fill it in in the column named date of payment.
In some rows the dates becomes 1900-01-00. Why does the function work on some dates but not others? The function is the same for all rows...
4 Replies
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.
