Harvey Balls for Due Date
Good morning, struggling with Harvey Balls this morning and creating a nested IF. My previous version did not take into consideration the status so as the entries aged, they eventually all turned red which skewed my metrics.
I am tracking the age of invoices and want to have the following conditions captured by a harvey ball indicating the health of the invoice.
1: If the invoice [Status] is marked as complete then Gray, (regardless of date)
2: If the [Due Date] is within the next 7 days, then Yellow,
3: If the [Due Date] is greater than 7 days in the future, then Green,
4: Then the [Due Date] is in the past which is Red.
Best Answer

=IF(Status@row = "COMPLETE", "Gray", IF([DUE DATE]@row < TODAY(), "Red", IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")))
Now I will explain a technique for writing long nested IF statements
You will need
1. Smartsheet (Works with Excel As Well)
2. Notepad or Notepad++.. A simple Text Editor/Scratchpad
Don't just jump in and start writing.. look at the logic cases
You said
1: If the invoice [Status] is marked as complete then Gray, (regardless of date)
2: If the [Due Date] is within the next 7 days, then Yellow,
3: If the [Due Date] is greater than 7 days in the future, then Green,
4: Then the [Due Date] is in the past which is Red.
So if we started with writing for green it looks like we would have a lot of checks for "IF(AND(Status@row=OPEN,[DUE DATE]=..."
But if we walk through the logic
IF#1. If it is COMPLETE.. no matter what the date then it is GREEN
Then we can assume everything else is OPEN and not bother checking for it
Then the determining Factor becomes the DUE DATE and only the DUE DATE
IF#2. If the DATE is in the past then it is RED
Then the next check becomes the 7 days..
But not really.. if you look at it all that is left is a yellow and another Green
Again If it is within 7 days then it is yellow..
If it is not yellow it must be green
Just going to spend a bit here..
See how the logic simplified and we don’t have to check for between today and 7 days because we checked for the lower limit in IF#2
And we also don’t have to have any statement to check for greater than 7 days as it is the only logic case left.
OK... So...
IF#3. If the DATE is Less than (or equal to) 7 days then YELLOW.
Leaving only Green Left
So we took that large problem and broke it down into 3 if statements
Now lets write them
I create 3 columns for the IF Statements
Call them IF1, IF2 and IF3
In IF1 we will write our first if Statement
=IF(Status@row = "COMPLETE", "Gray", "IF2")
Pretty Simple.. and you see how the "IF2" is simply saying.. well IF2 will deal with that..
Then in IF2 we look at the next case
=IF([DUE DATE]@row < TODAY(), "Red", "IF3")
Again Simple and for more, you need to look at the next IF statement
* Note that this will show a "COMPLETE" case in the past as RED.. but don’t worry.. remember IF1 catches that and it will never get here !!
IF 3 is simply..
=IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")
* Again note you will get a yellow from the above statement for COMPLETE in the past and overdue.. but IF1 and IF2 have taken care of those
Now comes the fun part !!
Copy each of those statements into notepad
Step 1
IF1: =IF(Status@row = "COMPLETE", "Gray", "IF2")
IF2: =IF([DUE DATE]@row < TODAY(), "Red", "IF3")
IF3: =IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")
Delete the equals on everything but IF1
Step 2
IF1: =IF(Status@row = "COMPLETE", "Gray", "IF2")
IF2: IF([DUE DATE]@row < TODAY(), "Red", "IF3")
IF3: IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")
Now Looking at IF1 replace where you see with IF2 with IF2
IF1: =IF(Status@row = "COMPLETE", "Gray", IF([DUE DATE]@row < TODAY(), "Red", "IF3"))
Now we have to do the same for IF3
IF1: =IF(Status@row = "COMPLETE", "Gray", IF([DUE DATE]@row < TODAY(), "Red", IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")))
That’s it now you have the big Nested If statement that should work the first time !!
Paste it in your ANDON Column (That’s what RYG is called in Lean Manufacturing)
And you are Done !!
Answers

=IF(Status@row = "COMPLETE", "Gray", IF([DUE DATE]@row < TODAY(), "Red", IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")))
Now I will explain a technique for writing long nested IF statements
You will need
1. Smartsheet (Works with Excel As Well)
2. Notepad or Notepad++.. A simple Text Editor/Scratchpad
Don't just jump in and start writing.. look at the logic cases
You said
1: If the invoice [Status] is marked as complete then Gray, (regardless of date)
2: If the [Due Date] is within the next 7 days, then Yellow,
3: If the [Due Date] is greater than 7 days in the future, then Green,
4: Then the [Due Date] is in the past which is Red.
So if we started with writing for green it looks like we would have a lot of checks for "IF(AND(Status@row=OPEN,[DUE DATE]=..."
But if we walk through the logic
IF#1. If it is COMPLETE.. no matter what the date then it is GREEN
Then we can assume everything else is OPEN and not bother checking for it
Then the determining Factor becomes the DUE DATE and only the DUE DATE
IF#2. If the DATE is in the past then it is RED
Then the next check becomes the 7 days..
But not really.. if you look at it all that is left is a yellow and another Green
Again If it is within 7 days then it is yellow..
If it is not yellow it must be green
Just going to spend a bit here..
See how the logic simplified and we don’t have to check for between today and 7 days because we checked for the lower limit in IF#2
And we also don’t have to have any statement to check for greater than 7 days as it is the only logic case left.
OK... So...
IF#3. If the DATE is Less than (or equal to) 7 days then YELLOW.
Leaving only Green Left
So we took that large problem and broke it down into 3 if statements
Now lets write them
I create 3 columns for the IF Statements
Call them IF1, IF2 and IF3
In IF1 we will write our first if Statement
=IF(Status@row = "COMPLETE", "Gray", "IF2")
Pretty Simple.. and you see how the "IF2" is simply saying.. well IF2 will deal with that..
Then in IF2 we look at the next case
=IF([DUE DATE]@row < TODAY(), "Red", "IF3")
Again Simple and for more, you need to look at the next IF statement
* Note that this will show a "COMPLETE" case in the past as RED.. but don’t worry.. remember IF1 catches that and it will never get here !!
IF 3 is simply..
=IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")
* Again note you will get a yellow from the above statement for COMPLETE in the past and overdue.. but IF1 and IF2 have taken care of those
Now comes the fun part !!
Copy each of those statements into notepad
Step 1
IF1: =IF(Status@row = "COMPLETE", "Gray", "IF2")
IF2: =IF([DUE DATE]@row < TODAY(), "Red", "IF3")
IF3: =IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")
Delete the equals on everything but IF1
Step 2
IF1: =IF(Status@row = "COMPLETE", "Gray", "IF2")
IF2: IF([DUE DATE]@row < TODAY(), "Red", "IF3")
IF3: IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")
Now Looking at IF1 replace where you see with IF2 with IF2
IF1: =IF(Status@row = "COMPLETE", "Gray", IF([DUE DATE]@row < TODAY(), "Red", "IF3"))
Now we have to do the same for IF3
IF1: =IF(Status@row = "COMPLETE", "Gray", IF([DUE DATE]@row < TODAY(), "Red", IF([DUE DATE]@row <= TODAY(7), "Yellow", "Green")))
That’s it now you have the big Nested If statement that should work the first time !!
Paste it in your ANDON Column (That’s what RYG is called in Lean Manufacturing)
And you are Done !!


@bcwilson.ca, thanks for the breakdown! I'm fairly comfortable writing nested IF's using the notepad trick but I have to say, your explanation was really helpful and I really appreciate the pointers; thanks for taking the extra time! The formula worked perfectly, much appreciated!

@Michael D. No problem I have been using that method for years and it helps!!
My Engineering brain likes things in nice little boxes with bows on them
Help Article Resources
Categories
Check out the Formula Handbook template!