# =IFERROR formula

Options

Hi

Within my project plan sheets I have 2 columns , On Time/Late and RAG.

On Time / Late Column Formula

=IFERROR(IF(AND([Original vs Current (days diff)]@row > 8, Status@row = "Complete", Milestones@row = "true"), "Late", IF(AND([Original vs Current (days diff)]@row <= 7, Status@row = "Complete", Milestones@row = "true"), "On Time")), "")

RAG Formula

=IFERROR(IF(Hierarchy3 > 1, IF([Current Due Date]3 - [Original Due Date]3 < 8, "Green", IF([Current Due Date]3 - [Original Due Date]3 <= 14, "Yellow", "Red"))), "")

Green with 7 days of original due date when task marked completed = On Time

Amber within 8-14 days of original due date task marked completed= Late

Red 15 days over original due date task marked completed = Late

I can't seem to get the Amber to show 'Late' in the On Time / Late column, when it's between 8-14 days over, and the task is marked completed. Any obvious reasons in my formula, tried to another another if statement.

Thanks.

Lindsay

Tags:

Options

I was about to solve it now - switched the 8 and 7 in the first formula for 'On Time / Late' to be the other way about and it's worked!

LW

=IFERROR(IF(AND([Original vs Current (days diff)]@row > 7, Status@row = "Complete", Milestones@row = "true"), "Late", IF(AND([Original vs Current (days diff)]@row <= 8, Status@row = "Complete", Milestones@row = "true"), "On Time")), "")

• ✭✭✭✭✭
Options

It looks like your missing cases where it's exactly 8.

Try ">=8" in the first formula and "<=8" in the second formula.

Let me know if that works,

Cheers,

Ramzi

Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (www.cedartreeconsulting.com)

Feel free to email me: ramzi@cedartreeconsulting.com

• Options

Thank you @Ramzi for responding to my query - but it did not work unfortunately.

• ✭✭✭✭✭✭
Options

The first formula is the only one that has a gap where you specify greater than 8 and less than or equal to 7 but you don't specify what to do for equal to 8.

Are you able to get it to show the other colors? Do you get an error or an unexpected result when it it between 8 and 14 days?

• Options

Thanks @Paul Newcome much appreciated.

I know see the gap, this originally was written but someone else and it was suggested I just update the 'numbers' within the formula.

The RAG does work yes, but no the On Time late for anything that is of RAG - Amber.

Thanks.

Lindsay

• ✭✭✭✭✭✭
Options

I don't understand. Which formula is not working correctly?

• Options

@Paul Newcome On Time / Late formula not working

=IFERROR(IF(AND([Original vs Current (days diff)]@row > 8, Status@row = "Complete", Milestones@row = "true"), "Late", IF(AND([Original vs Current (days diff)]@row <= 7, Status@row = "Complete", Milestones@row = "true"), "On Time")), "")

If between 8-14 days over original due date, the RAG goes amber but doesn't say 'Late'.

Lindsay

• ✭✭✭✭✭✭
Options

Your RAG formula does not specify anything about the Status or Milestones columns.

What exactly is in the [Original vs Current (days diff)] column?

• Options

@Paul Newcome its a text/number that displays the number of days off from the original date

=[Current Due Date]@row - [Original Due Date]

I'm having ago at amending the column formula based on what you've pointed out.

Thanks.

Lindsay

• ✭✭✭✭✭✭
Options

All you should have to do is make sure your requirements are met to include the Status column equaling "Complete" and the Milestones column equaling "true".

Does it show "On Time"?

Options

I was about to solve it now - switched the 8 and 7 in the first formula for 'On Time / Late' to be the other way about and it's worked!

LW

=IFERROR(IF(AND([Original vs Current (days diff)]@row > 7, Status@row = "Complete", Milestones@row = "true"), "Late", IF(AND([Original vs Current (days diff)]@row <= 8, Status@row = "Complete", Milestones@row = "true"), "On Time")), "")

• ✭✭✭✭✭✭
Options

Great! Glad you got it working!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!