Nested formula only partially working

[Jon_Pedersen]

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!

[ScottWorld · Accepted solution]

Jon_Pedersen:

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()))

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

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

[Matej_Mudrik]

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 :slightly_smiling_face:

[Kamille_Parks]

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.

[Jon_Pedersen]

That was simple!! Thanks so much.