
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 07, 2022 07:49 PM
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!
Solved! Go to Solution.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 07, 2022 08:13 PM
Before the second {Financial End Year Date}, you will need to put another IF(
Then, at the very end of your formula, add another )

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 07, 2022 08:13 PM
Before the second {Financial End Year Date}, you will need to put another IF(
Then, at the very end of your formula, add another )

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 07, 2022 08:16 PM
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:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 07, 2022 09:33 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jun 07, 2022 10:08 PM
That was simple!! Thanks so much.
