Help

Re: Troubleshooting Nested If formula (formula keep getting deleted)

Solved
Jump to Solution
679 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Jon_Pedersen
4 - Data Explorer
4 - Data Explorer

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!

1 Solution

Accepted Solutions
ScottWorld
18 - Pluto
18 - Pluto

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

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

See Solution in Thread

4 Replies 4
ScottWorld
18 - Pluto
18 - Pluto

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
6 - Interface Innovator
6 - Interface Innovator

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:

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.

That was simple!! Thanks so much.