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

 

[Togora]

Hi SkipVought,

I thought you might. Well, many thanks for the assistance you provided.

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]

1. get your explanation consisten.

2. provide the information asked for.

You FAILED on both counts!

If you care to focus like a laser and follow those guidelines, we could try again.

CLEAR, CONCISE and COMPLETE.

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

 

[Togora]

Hi SkipVought,

Many thanks for the offer however my last post prior to "I GIVE UP!" meets all the requirements and from that NOTHING will change. With that I have no additional information. If you are able to work on that great.

If you cannot than thank you again for your effort, extreme patients and the generosity of your invaluable time.

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

 

[Gruuuu]

Togora, in your conditional formatting, you can use the TODAY() function.

[tt]
=A1<TODAY()+14
=A1<TODAY()+7
=A1<TODAY()
[/tt]
This will update every time the workbook is opened, though. Does that help?

 

[Togora]

Hi Gruuu,

Many thanks for your submission. It appears your conditional formatting suggestion is the way to go. I have actually arrived at the answer but through a different route.

I first derived the following:


Green: =ROUND($D2-NOW(),0)<=7

Orange: =ROUND($D2-NOW(),0)>=8,ROUND($D2-NOW(),0)<=13))

Red: =ROUND($D2-NOW(),0)>=14


The problem with this solution is that one of the dates ended up being rounded the wrong way and thus provided the wrong answer. Also I noticed that this looked at the future and not into the past.

My second attempt was a little more elegant as follows:


Green: =TODAY()-$D2<=7

Orange: =TODAY()-$D2>=8,(TODAY()-$D2<=13))

Red: = TODAY()-$D2>=14


TODAY() made ROUND() and NOW() statements redundant. I also subtracted the cell contents from TODAY() which made everything look to the past.

These Formatting Conditions when tested meet the criteria I was looking for, and on logging in to Tek-Tips this evening I see Gruuuu has offered up a similiar solution, so many thanks for your excellent effort. I am not clever enough to determine if Gruuuu's solution is a more simplified form of my attempt, but I am sure someone out there will let me know.

Lastly, many thanks to those who have provided solid advice in helping with this problem and especially SkipVought who I feel was pushed to the absolute limit, but still offered to help find a solution.

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]

faq68-5827

Your DATE data has only the DATE component of Date/Time.

the TODAY() function, returns an integer date value.

the NOW() function, returns a full Date/Time value.

You would be better off, using the TODAY() function as gruuu had posted. It needs no rounding.

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

 

[Togora]

Hi SkipVought,

Thanks for your response. Your conclusions are what I discovered during testing, the TODAY() function is the way to go. It is also what I have entered into our spreadsheet earlier to day.

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

 

[Gruuuu]

Also, Togora, you don't need to 'target' the 8-14 range. The conditional formatting stops on the first condition it meets (using the priority of the condition numbers "1-3").

If that makes sense, it should help with conditional formats in the future.
If not, nevermind! Enjoy your now-working colors

 

[Togora]

Hi Gruuuu,

I understand what you mean however, when I copy and pasted in your examples they didn't work as well as my final solution. So my question is, is you suggestion regarding not needing to "target" the range so in every case?

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

 

[Gruuuu]

It appears that my criteria were in the wrong sequence. You should be able to sequence them correctly.

 

[Togora]

Hi Gruuuu,

I did. 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