Discussion IF-function for date in excel, some dates becomes 0 in Excel
https://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488474#M66735
<P>Hi!</P><P>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. </P><P>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)))))</P><P> </P><P>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. </P><P> </P><P>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. <BR />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... </P>Thu, 25 Jun 2020 09:24:42 GMTAkila19932020-06-25T09:24:42ZIF-function for date in excel, some dates becomes 0
https://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488474#M66735
<P>Hi!</P><P>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. </P><P>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)))))</P><P> </P><P>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. </P><P> </P><P>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. <BR />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... </P>Thu, 25 Jun 2020 09:24:42 GMThttps://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488474#M66735Akila19932020-06-25T09:24:42ZRe: IF-function for date in excel, some dates becomes 0
https://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488524#M66736
<P>Hi Akila,</P><P> </P><P>Some dates are returning 0 because the first part of your formula <FONT color="#3366FF">IF(B13=420,C13+$O$6,IF(B13=500,C13+$O$7))</FONT> 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.<BR /><BR />The same thing also for the second part of your formula <FONT color="#3366FF">(IF(B6=150,(C6+$O$8)*(IF(B6=320,(C6+$O$9),0))))); <FONT color="#000000">it is only referencing two values.<BR /><BR />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.<BR /><BR />I hope this helps.</FONT></FONT></P>Thu, 25 Jun 2020 09:48:42 GMThttps://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488524#M66736BaloDeChamp2020-06-25T09:48:42ZRe: IF-function for date in excel, some dates becomes 0
https://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488545#M66738
<P>Thank you so much for your answer, and quick reply!<BR />So basically, what I have done is that I have divided the formula into two parts?</P><P> </P><P>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.</P><P> </P><P>Regards!</P>Thu, 25 Jun 2020 09:54:50 GMThttps://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488545#M66738Akila19932020-06-25T09:54:50ZRe: IF-function for date in excel, some dates becomes 0
https://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488560#M66739
<P>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!!! <LI-EMOJI id="lia_slightly-smiling-face" title=":slightly_smiling_face:"></LI-EMOJI> </P>Thu, 25 Jun 2020 10:05:52 GMThttps://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488560#M66739Akila19932020-06-25T10:05:52ZRe: IF-function for date in excel, some dates becomes 0
https://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488578#M66741
<P>Hello Akila,</P><P> </P><P>You can use the IFS function to fix the problem - see below.</P><P><FONT color="#3366FF">=IFS(B6=420,C6+$O$6,B6=500,C6+$O$7,B6=150,C6+$O$8,B6=320,C6+$O$9)</FONT></P><P> </P><P>This should work fine and you do not need the second part of the formula again.<BR /><BR />I hope this helps.</P><P> </P><P> </P>Thu, 25 Jun 2020 10:20:06 GMThttps://techcommunity.microsoft.com/t5/excel/if-function-for-date-in-excel-some-dates-becomes-0/m-p/1488578#M66741BaloDeChamp2020-06-25T10:20:06Z