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