=IFERROR formula

Lindsay Wilson

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

Lindsay Wilson

@Paul Newcome Thanks for your reply.

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

Ramzi K

Hi @Lindsay Wilson,

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

Lindsay Wilson

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

Paul Newcome

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?

Lindsay Wilson

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

Paul Newcome

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

Lindsay Wilson

@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

Paul Newcome

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

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

Lindsay Wilson

@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

Paul Newcome

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

Lindsay Wilson

@Paul Newcome Thanks for your reply.

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

Paul Newcome

Great! Glad you got it working!