Skip to main content
Solved

IF Function Help, Nested

  • November 21, 2022
  • 7 replies
  • 72 views
W
Forum|alt.badge.img+7

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.

Best answer by Wendy_Yelsik

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

Kamille_Parks11
Forum|alt.badge.img+27

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"
))))
W
Forum|alt.badge.img+7
  • Wendy_Yelsik
  • Author
  • Inspiring
  • November 21, 2022

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

W
Forum|alt.badge.img+7
  • Wendy_Yelsik
  • Author
  • Inspiring
  • November 21, 2022

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?

Kamille_Parks11
Forum|alt.badge.img+27

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"
)))))
W
Forum|alt.badge.img+7
  • Wendy_Yelsik
  • Author
  • Inspiring
  • November 21, 2022
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

Kamille_Parks11
Forum|alt.badge.img+27

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


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

W
Forum|alt.badge.img+7
  • Wendy_Yelsik
  • Author
  • Inspiring
  • Answer
  • November 22, 2022

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

Terms & ConditionsAccessibility statement