Help

Re: IF Function Help, Nested

Solved
Jump to Solution
2471 5
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: ”
)))))