Help me develop this formula?

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
383
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]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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:
Upvote 0
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?
 
Upvote 0
=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:
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top