Help me develop this formula?

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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]
 

Some videos you may like

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
Joined
Nov 7, 2006
Messages
8,350
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
 
Last edited:

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2006
Messages
8,350
=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
Joined
Nov 7, 2006
Messages
8,350

ADVERTISEMENT

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
Joined
Oct 20, 2010
Messages
380
Office Version
  1. 365
Platform
  1. Windows
Wicked, I'll give it a go! Right, let's see what I can do....
 

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 7, 2006
Messages
8,350
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
Joined
Oct 20, 2010
Messages
380
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,805
Messages
5,524,979
Members
409,613
Latest member
Dalex100

This Week's Hot Topics

Top