Need help combining these 2 IF statements

I can get both of these to work separately, but when I try combining them, using IF(OR or IF(AND or even just another IF(, the "Yellow" part won't work. Suggestions, please?

=IF([Milestone Due Date]@row > ([Today Helper]@row + 30), "Green", IF([Milestone Due Date]@row > ([Today Helper]@row + 7), "Yellow", "Red"))

=IF([Milestone Completed Date]@row > [Milestone Due Date]@row, "Red", IF([Milestone Completed Date]@row < [Milestone Due Date]@row, "Green"))

Thank you,

Ashley

Find more posts tagged with

IF(OR

IF(AND

IF(

Accepted answers

Nick Korna

Hi @Ashley McAdoo,

Something like this?

=IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > TODAY(30)), "Green", IF([Milestone Due Date]@row > TODAY(7), "Yellow", "Red"))

Sample output:

If your want the +7/+30 to be yellow/green respectively, change the formula slightly:

=IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row >= TODAY(30)), "Green", IF([Milestone Due Date]@row >= TODAY(7), "Yellow", "Red"))

For:

Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!

Mark.poole

@Ashley McAdoo

Further investigating shows that in both cases @Nick Korna forgot to account for when the completed date is Both after the due date and less then 7 days. Its all due to how the And() function works in the first portion of the formula. His corrected formula is .

=IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > TODAY(30)), "Green", IF(OR([Milestone Completed Date]@row > [Milestone Due Date]@row, [Milestone Due Date]@row < TODAY(7)), "Red", "Yellow"))

If your trying to bypass the issues with the today formula then do

=IF(OR(AND([Milestone Completed Date]@row < [Milestone Due Date]@row, [Milestone Completed Date]@row <> ""), [Milestone Due Date]@row > [Today Helper]@row+30), "Green", IF(OR([Milestone Completed Date]@row > [Milestone Due Date]@row, [Milestone Due Date]@row < [Today Helper]@row+7), "Red", "Yellow"))

Below is the outcome of both versions of the formula.

All comments

Mark.poole

@Ashley McAdoo

To be sure I got it. You want it to flag red if the completed date is over or equal to 30 days the due date. Yellow if up to 7 days over the due date. And green if before the due date?

Nick Korna

Hi @Ashley McAdoo,

Something like this?

Sample output:

If your want the +7/+30 to be yellow/green respectively, change the formula slightly:

For:

Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!

Ashley McAdoo

@Mark.poole,

I would like it to turn red if the due date is within 7 days or if the completion date is after the due date; green if the due date is more then 30 days out or if the completion date is before the due date, and yellow if the due date is between 7 and 30 days from today.

Thanks,

Ashley

Mark.poole

@Ashley McAdoo, @Nick Korna

Based on my test Nick's Solution did not quite work the way you wanted but was very close. IT also looks like she was trying to implement a work around for how the Today() function works, From what I could tell mine does exactly what you want. Nick if you could shorten the formula for Ashley for a better solution I welcome it as it would be a learning experience for me as well.

=IF(ISBLANK([Milestone Completed Date]@row), IF([Milestone Due Date]@row > [Today Helper]@row + 30, "Green", IF(AND([Milestone Due Date]@row > [Today Helper]@row + 7, [Milestone Due Date]@row < [Today Helper]@row + 30), "Yellow", IF([Milestone Due Date]@row <= [Today Helper]@row + 7, "Red"))), IF([Milestone Completed Date]@row > [Milestone Due Date]@row, "Red", "Green"))

Mark.poole

@Ashley McAdoo

If your trying to bypass the issues with the today formula then do

Below is the outcome of both versions of the formula.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.