Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

# IF Function Help, Nested

Topic Labels: Formulas
Solved
329 7
cancel
Showing results for
Did you mean:
6 - Interface Innovator

Looking for help creating a formula to output one of these answers based on a person’s meeting start date and the completed date of the program.
Meeting Status field (formula):
• Done
• ToDO
• NA
• Upcoming

My fields are-
• Meeting Start Date –
• Program Date
• Current Date Field

1. If a person has a meeting start date of 11/1/2022 and the program date is 12/1/2022 then the field should output “NA”
2. If a program is scheduled for a future date, then the output should be “upcoming”-
3. If a person hasn’t completed the program, the output should be “ToDo”
4. If a person completed the program, then the output should be “done”

I appreciate any help.

1 Solution

Accepted Solutions
6 - Interface Innovator

Hi, Kamille. I need a tweak to get the NA to work. For example, an employee isn’t required to complete a program until 12/1/2022. In the November program field he needs an NA. The formula is returning a missed (but with my emojis). Here is my formula with my fields. I may not understand your NA formula and the meaning of the dates in “”.

IF(
AND(
{Meeting Start Date},
{NovBeg(hide)}
),
IF(
AND(
DATETIME_FORMAT({Meeting Start Date}, “YYYYMMDD”) = “20221101”,
** DATETIME_FORMAT({NovBeg(hide)}, “YYYYMMDD”) = “20221101”**
),
“NA”,
IF(
{NovBeg(hide)} > TODAY(),
“upcoming”,
IF(
NOT({Nov22}),
“ :warning: :triangular_flag_on_post: ”,
IF(
{Nov22}=“Nov 2022”,
“ :heavy_check_mark: ”,
“ :warning: :triangular_flag_on_post: ”
)))))

7 Replies 7
16 - Uranus

Try:

``````IF(
AND(
{Meeting Start Date},
{Program Date}
),
IF(
AND(
DATETIME_FORMAT({Meeting Start Date}, "YYYYMMDD") = "20221101",
DATETIME_FORMAT({Program Date}, "YYYYMMDD") = "20221201"
),
"NA",
IF(
{Program Date} > TODAY(),
"upcoming",
IF(
NOT({Program Date}),
"ToDo",
"done"
))))
``````
6 - Interface Innovator

Yes, that worked! Thank you. Wendy

6 - Interface Innovator

If my result is “done” It has to be verified first. I have another field called MtgDone which requires HR’s checkmark for the employee to get credit. Is there a way to add that into this function?

16 - Uranus
``````IF(
AND(
{Meeting Start Date},
{Program Date}
),
IF(
AND(
DATETIME_FORMAT({Meeting Start Date}, "YYYYMMDD") = "20221101",
DATETIME_FORMAT({Program Date}, "YYYYMMDD") = "20221201"
),
"NA",
IF(
{Program Date} > TODAY(),
"upcoming",
IF(
NOT({Program Date}),
"ToDo",
IF(
{MtgDone},
"done",
"needs verification"
)))))
``````
6 - Interface Innovator

Wow! That works. Thank you. I appreciate all the help. Wendy

16 - Uranus

6 - Interface Innovator

Hi, Kamille. I need a tweak to get the NA to work. For example, an employee isn’t required to complete a program until 12/1/2022. In the November program field he needs an NA. The formula is returning a missed (but with my emojis). Here is my formula with my fields. I may not understand your NA formula and the meaning of the dates in “”.

IF(
AND(
{Meeting Start Date},
{NovBeg(hide)}
),
IF(
AND(
DATETIME_FORMAT({Meeting Start Date}, “YYYYMMDD”) = “20221101”,
** DATETIME_FORMAT({NovBeg(hide)}, “YYYYMMDD”) = “20221101”**
),
“NA”,
IF(
{NovBeg(hide)} > TODAY(),
“upcoming”,
IF(
NOT({Nov22}),
“ :warning: :triangular_flag_on_post: ”,
IF(
{Nov22}=“Nov 2022”,
“ :heavy_check_mark: ”,
“ :warning: :triangular_flag_on_post: ”
)))))