Skip to main content

Hi Community,



I’m having trouble creating a nested if statement, where the condition is to identify two options from the field “Financial End Year Date” (single select field), and return two different values (for ‘December 31’ data - return ‘8/15/2022’, and for ‘March 31’ data - return ‘11/15/2022’, and if neither, return ‘4/30/2022’.



The nested if formula I created and tried is as below:





  • IF({Financial End Year Date}=“December 31”,“8/15”&"/"&YEAR(NOW()),{Financial End Year Date}=“March 31”,“11/15”&"/"&YEAR(NOW()),“4/30”&"/"&YEAR(NOW()))




When I run this formula, the field shows either “8/15/2022” or “true”, where the “true” should instead be “11/15/2022”. For the ‘neither’ options, it returns a blank cell.



What’s more confusing is, after I save the formula and click the configuration again, the formula is automatically corrected to below:





  • IF({Financial End Year Date}=“December 31”,“8/15”&"/"&YEAR(NOW()),{Financial End Year Date}=“March 31”)




Could anyone help troubleshoot and fix this nested if formala please? Thank you!



Before the second {Financial End Year Date}, you will need to put another IF(



Then, at the very end of your formula, add another )

maybe mine imagination is bad,


but


it should be


IF december,“this”, if march, “this”,otherwiser “this”



IF(


{Financial End Year Date}=“December 31”, “8/15”&"/"&YEAR(NOW(),



IF(


{Financial End Year Date}=“March 31”, “11/15”&"/"&YEAR(NOW(), “4/30”&"/"&YEAR(NOW()


))


writing and copying this on cell phone, so be patient 🙂

The formula can also be corrected to:



DATETIME_PARSE(


  SWITCH(


    {Financial End Year Date},


    "December 31", "8/15",


    "March 31", "11/15",


    "4/30"


  ), 


  "M/D"


)


^ this will produce real dates instead of strings. Feeding DateTimeParse just a month and day (or just a month), will default it to the current year. Using SWITCH statements instead of nested IFs in times like these helps highlight where duplicative functions can be streamlined.



Before the second {Financial End Year Date}, you will need to put another IF(



Then, at the very end of your formula, add another )


That was simple!! Thanks so much.

Reply


Terms & Conditions