# Help me develop this formula?

#### halesowenmum

##### Active Member
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]

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### Special-K99

##### Well-known Member
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

Last edited:

#### halesowenmum

##### Active Member
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

##### Well-known Member
=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...

Last edited:

#### Special-K99

##### Well-known Member

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

Last edited:

#### halesowenmum

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

#### halesowenmum

##### Active Member

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

##### Well-known Member
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.

Last edited:

#### halesowenmum

##### Active Member
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.

Replies
3
Views
71
Replies
0
Views
40
Replies
2
Views
256
Replies
7
Views
34
Replies
2
Views
38