Help me develop this formula?

[halesowenmum]

Hi all

I have the following formula which is working great (as far as it goes):

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif][FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(AF6>AA6,"Not due yet",IF(AF6>AA6+7,"~ 7 days late","+ 7 days late"))[/FONT]
AF6 is Last Review Date
AA6 is todays date.

So the formula is looking to show:
If the Last Review date is overdue return one of the messages based on how over it is, and if its actually not due yet, say so.

But what I want to do though is be able to expand on the lateness element so:
Where it's 0 - 7 days overdue "0-7 days overdue" PALE ORANGE

Where it's 8 - 30 days overdue "8-30 days overdue" BRIGHT ORANGE
Where it's 31+ days overdue "+31 days overdue". " BRIGHT RED FILL AND BOLD WHITE TEXT

How would I use CF with this formula in order to show the colours indicated above???

Thank you. ​

_{^{<strike>
</strike>}}[/FONT]

 

[Special-K99]

Your formula is wrong.
The second IF wont return "~ 7 days late" ever unless the value in AA6 changes.

Consider this:

AF6 is 27
AA6 is 13

IF AF6 > AA6 returns Not due yet.

When AF6 is 21 and AA6 is 13 AF6 > AA6 by +7 so the second IF will return "~ 7 days late". However your first IF (AF6 > AA6) will override that so you'll never see "~7 days late".
You will however see +7 days late when AF6 < = AA6

 

[halesowenmum]

Your formula is wrong.
The second IF wont return "~ 7 days late" ever unless the value in AA6 changes.

Consider this:

AF6 is 27
AA6 is 13

IF AF6 > AA6 returns Not due yet.

When AF6 is 21 and AA6 is 13 AF6 > AA6 by +7 so the second IF will return "~ 7 days late". However your first IF (AF6 > AA6) will override that so you'll never see "~7 days late".
You will however see +7 days late when AF6 < = AA6

Ok! I didn't build this formula myself so have inherited whatever issues it has and not being an expert (hence being on here) wasn't aware of that until you've kindly told me (thank you for advising).

What should it be then?

 

[Special-K99]

=IF(AF6 > AA6,"Not due yet",IF(AF6 < AA6-7,"+ 7 days late","~ 7 days late"))

If Review date > today then its not due
Otherwise
If the review date is less than today - 7 then its more than 7 days late
otherwise
the review date is less than 7 days late

Working on the other parts...

 

[Special-K99]

Revised formula

=IF(AF6 > AA6, "Not due yet",LOOKUP(AA6-AF6, {0,8,31},{"0-7","8-30","+31"})&" days overdue"))

Condtitional Formatting (untested)

Select the range to highlight

Conditional Formatting
New Rule
Use a formula to determine...

=AND(AF6<=AA6,AA6-AF6)<=7 Pale Orange

=AND(AF6<=AA6,AA6-AF6)<=8 Bright Orange

=AND(AF6<=AA6,AA6-AF6)<=31 BRIGHT RED FILL AND BOLD WHITE TEXT

Format as required

 

[halesowenmum]

Wicked, I'll give it a go! Right, let's see what I can do....

 

[halesowenmum]

Well, that worked an absolute treat - thank you!

I couldn't enter the formulae into the CF as suggested though - something about the format of the formula not being allowed in CF, so currently I've just used the 'contains' and 'equal to' CF option and referenced the various text outputs which is working fine but just need to query one thing.

Because I didn't have any examples where today's date matched the last review date, I changed the due date on one so it did match to test if it would come up with the 'Not due yet' wording. It didn't though, it came up with '0-7 days overdue' instead. Not sure why this is?

 

[Special-K99]

Dont enter the colours as part of the formula, just the formula before them, then select what colour you want.

If the due date matches (is equal to) the last review date then it lies within the 0-7 days overdue range, ie 0 days difference.
If that's not correct then your description shouldnt say "0-7 days" it should say "1-7 days" and the formula will need changing slightly.

 

[halesowenmum]

I didn't enter the colours - it was the construction of the formulas it didn't like.

I don't think the 0-7 days thing is too much of an issue really tbh; it's more about showing which project managers can't be bothered to review their risks regularly!!! (So the longer times are of more interest in this case). So thank you, I'm very happy with it indeed and thank you for your help.