IF Function Help, Nested

[Wendy_Yelsik]

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.

[Kamille_Parks]

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

[Wendy_Yelsik]

Yes, that worked! Thank you. Wendy

[Wendy_Yelsik]

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?

[Kamille_Parks]

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

[Wendy_Yelsik]

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

[Kamille_Parks]

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

[Wendy_Yelsik · Accepted solution]

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: ”
)))))