Sort and highlight rows by date? 2

[Togora]

Hi,

I am attempting to sort a list with a date criteria and then dependant on the criteria the row would be coloured.

For example:

Criterion (in days): 1-7 = Green, 8-21 = Orange and 22+ = Red

DMD DATE DMD VOUCHER EXPECTED RECEIPT DATE
27-Jan-11 5718 119457 07-Feb-11
08-Feb-11 8686 120963 10-Feb-11
10-Feb-11 10-Feb-11
27-Jan-11 10-Feb-11
07-Feb-11 11-Feb-11
09-Feb-11 11-Feb-11
15-Feb-11 14-Feb-11
15-Feb-11 15-Feb-11


Therefore, if the "DMD Date" matches or exceeds the above criterion compared to the "Expected Receipt Date" then that row would change colour to show the urgency. The larger the difference eg 22+ the more urgent. Finally, the rows would be sorted in order of priority with Red at the top then Orange finally Green.

I understand that I could use "Conditional Formatting" to achieve some of what I want however, I have tried but fail because there appears to be too many conditions.

Any assistance with this would be gratefully received

Regards

Togora

Nancy Astor: “Sir, if you were my husband, I would give you poison.”
Churchill: “If I were your husband I would take it.”

Sir Winston Churchill

 

[SkipVought]

hi,

Red at the top [red]then[/red] Orange [red]finally[/red] Green.

So what is the LOGIC for [red]then[/red] and [red]finally[/red]?

You need to be very specific and give relevant examples.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

You have specified 3 criteria, which is exactly how many conditional formatting supports.

Less than 8

Less than 22

Greater than or equal to 22

 

[SkipVought]

oops, I seem to have missed something! SORRY!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Togora]

Hi SkipVought,

Thank you for highlighting "Red at the top then Orange finally Green." in quotes. I see why you did this, it should have read

"Red at the top then Orange and finally Green".

My English grammar is slipping, sorry.

Togora

Nancy Astor: “Sir, if you were my husband, I would give you poison.”
Churchill: “If I were your husband I would take it.”

Sir Winston Churchill

 

[Togora]

Hi mintjulep,

The criterion is:

Equal to or between 1 -> 7 (but not zero)
Equal to or between 8 -> 20
Equal to or greater than 21

Then the row requires sorting and finally coloured green, orange or red.

I hope this is a little clearer.

Thanks for the quick reply though.

Nancy Astor: “Sir, if you were my husband, I would give you poison.”
Churchill: “If I were your husband I would take it.”

Sir Winston Churchill

 

[mintjulep]

What about, for example 7.5 days?

 

[Togora]

Hi mintjulep,

It is date based therefore you would never get 7.5 days and would be day 8.

Thanks again,

Togora

Nancy Astor: “Sir, if you were my husband, I would give you poison.”
Churchill: “If I were your husband I would take it.”

Sir Winston Churchill

 

[SkipVought]

select all your DATA cells

In CF
[tt]
1-7 = Green, 8-21 = Orange and 22+ = Red

GREEN: =AND($A4<>0,ABS($A2-$A4)<=7)
ORANGE: =AND($A4<>0,ABS($A2-$A4)>7,ABS($A2-$A4)<=21)
RED: =AND($A4<>0,ABS($A2-$A4)>21)
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Togora]

Hi SkipVought,

Ok, You are good, but are you great?

I plugged in your formulas and they worked, not surprising I think I hear you say. But, there had to be a but. Part of the criteria was between 1-7. The formula colours the row green when it is the same day eg 28/03/11 and 28/03/11 when it shouldn't change until the 29/03/11.

I tried to change the formula to get this however it wouldn't play ball. Any suggestions?

A very big thanks for your help so far.

Togora

Nancy Astor: “Sir, if you were my husband, I would give you poison.”
Churchill: “If I were your husband I would take it.”

Sir Winston Churchill

 

[SkipVought]

please post an explicit example of a ROW that meets that criteria, similar to your original example.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gruuuu]

Methinks that it needs to be BETWEEN 1 & 7, not less than 7. Just add another criteria in the AND() function:
[tt]
=AND($A4<>0,ABS($A2-$A4)<=7,ABS($A2-$A4)>0)
[/tt]

 

[Togora]

Hi SkipVought,

Sorry for not getting back sooner here is the sample spreadsheet I use:


DMD DATE DMD VOUCHER ISSUE
10-Feb-11 5718 119457 07-Feb-11
27-Mar-11 8686 120963 10-Feb-11
10-Feb-11 10-Apr-11
27-Jan-10 11-Apr-11
07-Feb-11 12-Apr-11
09-Feb-11 13-Apr-11
19-Feb-11 14-Apr-11
15-Feb-11 15-Apr-11
15-Feb-11 16-Apr-11
15-Feb-11 17-Apr-11
15-Feb-11 18-Apr-11
02-Feb-11 15-Feb-11
03-Feb-11 15-Feb-11
14-Feb-11 15-Feb-11
16-Mar-11 16-Mar-11
17-Mar-11 17-Mar-11
18-Mar-11 18-Mar-11
19-Mar-11 19-Mar-11
20-Mar-11 20-Mar-11
21-Mar-11 21-Mar-11
22-Mar-11 22-Mar-11
23-Mar-11 23-Mar-11
24-Mar-11 24-Mar-11
25-Mar-11 25-Mar-11
26-Mar-11 26-Mar-11
27-Mar-11 27-Mar-11
28-Mar-11 28-Mar-11
29-Mar-11 29-Mar-11
17-Feb-11 17-Feb-11
18-Feb-11 18-Feb-11
18-Feb-11 18-Feb-11
18-Feb-11 18-Feb-11

Thanks again

Togora

Hi Gruuu,

I tried out your example but for some reason it didn't appear to work correctly. But when I get back from work tonight I will give it another go.

Thanks for the reply

Togora

Nancy Astor: “Sir, if you were my husband, I would give you poison.”
Churchill: “If I were your husband I would take it.”

Sir Winston Churchill

 

[SkipVought]

Please explain the issue with respect to a specific instance in your example.

Please be VERY sepcific and detailed.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Togora]

Hi SkipVought,

Sorry if I have wasted your time with this problem. I have not only confused you but also myself.

What we are attempting to do is show on a spreadsheet demands which have not arrived on time and by way of colour and date how late the demands are in arriving.

Therefore:

White = Dmd date = Day 0

Green = Dmd Date + 1 -> 7 days

Orange = Dmd Date + 8 -> 13 days

Red = Dmd Date + 14+ days

Therefore the Dmd date is NOT tied to the "issue date" but rather the day the demand was placed. Sorry for this confusion.

Here is an example spreadsheet:


DMD DATE DMD VOUCHER ISSUE
10-Feb-11 5718 119457 07-Feb-11
27-Mar-11 8686 120963 10-Feb-11
10-Feb-11 8687 120963 10-Apr-11
27-Jan-10 8688 120964 11-Apr-11
07-Feb-11 8689 120965 12-Apr-11
09-Feb-11 8690 120966 13-Apr-11
19-Feb-11 8691 120967 14-Apr-11
15-Feb-11 8692 120968 15-Apr-11
15-Feb-11 8693 120969 16-Apr-11
15-Feb-11 8694 120970 17-Apr-11
15-Feb-11 8695 120971 18-Apr-11
02-Feb-11 8696 120972 15-Feb-11
03-Feb-11 8697 120973 15-Feb-11
14-Feb-11 8698 120974 15-Feb-11
16-Mar-11 8699 120975 16-Jan-11
23-Mar-11 8700 120976 17-Jan-11
18-Mar-11 8701 120977 18-Jan-11
19-Mar-11 8702 120978 19-Jan-11
03-Mar-11 8703 120979 20-Mar-11
04-Mar-11 8704 120980 21-Mar-11
05-Mar-11 8705 120981 22-Mar-11
06-Mar-11 8706 120982 23-Mar-11
07-Mar-11 8707 120983 24-Mar-11
25-Mar-11 8708 120984 25-Mar-11
26-Mar-11 8709 120985 26-Mar-11
27-Mar-11 8710 120986 27-Mar-11
28-Mar-11 8711 120987 28-Mar-11
29-Mar-11 8712 120988 29-Mar-11
17-Feb-11 8713 120989 17-Feb-11
18-Feb-11 8714 120990 18-Feb-11
18-Feb-11 8715 120991 18-Feb-11
18-Feb-11 8716 120992 18-Feb-11

I hope I have made this a bit clearer and once more sorry about the confusion.

Thanks again for your help

Togora

Nancy Astor: “Sir, if you were my husband, I would give you poison.”
Churchill: “If I were your husband I would take it.”

Sir Winston Churchill

 

[mintjulep]

I hope I have made this a bit clearer

No. You haven't.

In your original post you had "DMD DATE" and "EXPECTED RECEIPT DATE"

Now you have introduced "issue date" in your latest post.

Before you can solve a problem you have to understand the problem. And right now it doesn't seem that you understand what it is that you want or need to do.

This is compounded by your inability to clearly communicate anything at all.

We don't know what a "demand" is. We don't know what an "issue date" is, or what an "expected receipt date" means, or if issue date and expected receipt date are actually the same date.

In fact, we know nothing about your process at all.

Post some complete data.

Define all of the relevant dates.

Answer all of Skip's questions.

 

[SkipVought]

I need you to state on a sample row, what color you expect, that is a different result from my posted solution.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Togora]

Hi SkipVought,

You did as I asked and had taken the difference between the "DMD Date" and the "Issue Date". However I should have read the notes I took on this problem. Sorry!

What I should have asked for was, as stated in my last post, the DMD Date plus the requisite number of days which denotes the colour displayed.

For example:

1. I enter into "DMD Date" todays date. That entry remains "White".

2. The following day this will change to "Green" if the order (DMD = Demand = Order, company lingo, sorry) is still outstanding.

3. If "order" is still outstanding on day 8 it turns "Orange".

4. If "order" is still outstanding on day 14 (and onwards) it turns "Red".

This is the step-by-step logic of the process. Thinking about it you don't really need the spreadsheet sample to figure this out. But then again you are the expert and will know better than me.

When I first looked at this problem I thought it would need to compare todays date with the date in the "DMD Date" column. This would determine whether it needed to change or not and if so which colour it needed to change to. This is why I felt conditional formatting was of no use.

Many thanks again for your patience and assistance.

Togora

Nancy Astor: “Sir, if you were my husband, I would give you poison.”
Churchill: “If I were your husband I would take it.”

Sir Winston Churchill

 

[SkipVought]

I GIVE UP!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mintjulep]

I gave up a while ago.