Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

IF Function Help, Nested

Topic Labels: Formulas
Solved
Jump to Solution
2835 7
cancel
Showing results for 
Search instead for 
Did you mean: 
Wendy_Yelsik
6 - Interface Innovator
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
Wendy_Yelsik
6 - Interface Innovator
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: ”
)))))

See Solution in Thread

7 Replies 7

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

Yes, that worked! Thank you. Wendy

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?

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

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

Good, please mark my second reply as the solution to close this thread.

Wendy_Yelsik
6 - Interface Innovator
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: ”
)))))