Highlight upcoming days

[John19]

I want numbers in a column, that correspond to upcoming days of the month highlighted (font colour green). Please see attached sheet, I have tried to explain on it what I want.


Thank you

[FDibbins]

I think you forgot to attach the file?

However, I think what you need is Conditional Formatting.

1. highlight the range you want to apply the conditional formatting to
2. on the home tab, styles, select CF
3. select new rule, select use formula
4. enter = $A2<=today()+5

Where A2 contains your date
5 is the days ahead you want to test for

[John19]

i have attached file again. please take a look at it, i've explain
exactly what i mean on it.

thanks

[John19]

i've tested your suggestion, not what i was looking for.

what i need is:
I want the next upcoming day(s) (closest to today) highlighted green. On the same day it should go back to normal.

So today is 01/08/15, closest upcoming date is 2 , the 2's anywhere in the list should turn green.

On 02/08/15, only the 3's in the list should turn green.

On 03/08/15 the next upcoming day on the list is 6.

On 04/08/15 still 6.

On 05/08/15 still 6.

On 06/08/15 its 7's.

On 07/08/15 its next upcoming day is 13.

On 13/08/15 its 22.

and so on.

On 28/08/15 its 2.

Note: if the numbers are todays day, it should not be highlighted - be normal.dates.xlsx

[FDibbins]

Try this, based on a formula from member (Guru) benishiryo...
=INDEX($H$9:$H$24,MATCH(MIN(IF($H$9:$H$24>DAY(I3),$H$9:$H$24)),IF($H$9:$H$24>DAY(I3),$H$9:$H$24),0))

This is an ARRAY formula ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

edit: sorry, you wanted this fo highlight the cells. Give me a minute to work on that

[FDibbins]

OK, here you go.

follow the steps in my post #2, but use this formula...
=H10=INDEX($H$9:$H$24,MATCH(MIN(IF($H$9:$H$24>DAY($I$3),$H$9:$H$24)),IF($H$9:$H$24>DAY($I$3),$H$9:$H$24),0))

Format fill as required

[John19]

almost perfect, just at the end of the month - say on 28/08/15, the next upcoming date on my list is 2 - but its not highlighted

thanks

[John19]

in other words, at the end of the month, it does look at the next month

[FDibbins]

I see a problem here with just using days. If it needs to look at "next" month, how is excel to know that the 2 in H15 in your sample is 2 July, 2 Aug or 2 Sept?

I suggest that instead of just using day values, you use actual dates. You can format them to just show days if you want, but this way, excel will know that "2" Sept needs to be selected for "29" Aug etc

The CF formula would then become...
=H10=INDEX($H$9:$H$24,MATCH(MIN(IF($H$9:$H$24>$I$3,$H$9:$H$24)),IF($H$9:$H$24>$I$3,$H$9:$H$24),0))

[John19]

I need to use just day values in that column, any other way i can solve this? maybe a second CF just for the end of the month. I want to keep your formula because its perfect for the other dates.

[FDibbins]

I come back to my observation in post # 9...

I see a problem here with just using days. If it needs to look at "next" month, how is excel to know that the 2 in H15 in your sample is 2 July, 2 Aug or 2 Sept?

[John19]

the month (july, aug, sep,...) doesn't matter, we know the max value for current month is 31, maybe thinking around this i could device a formula?

sorry if i'm not making sence, just think there must be a way to do it

[FDibbins]

What you need to understand about dates in excel is that a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Sat Aug 2015) is actually 42217.

So when excel sees 2 - or 5, 12 50 - it does not see a date, it just sees some random number that is in noway related to a date. Thats why I am trying to get this back to actual dates. Where are these numbers coming from? Perhaps knowing taht will help come up with an answer?

[John19]

the numbers are dates of the month, but they are fixed and remain the same irrespective of month or year. The events occur on those days of every month. I suppose you could take a fixed date as the start for each of the numbers.

[FDibbins]

Here is what Im getting at...

lets just say you have a 7, and the date is Aug 1 15.
So, the formula will look and see there is nothing btw today and 7, so 7 will be highlighted...until Aug 7 15, then it will look for a later date, and ignore 7
So we get to Aug 28, and there is no other days for the month. It has nothing past 28 to look at, so it wont highlight anything - remember, we are past 7, so that wont be considered

What is likely to be the widest gap between a day and the date - towards the end of the month? Im thinking of somehow getting a helper to test against, with a formula that looks at the date, compares it with your number, then - when necessary, re-starts the test.

(Im groping at straws here lol)

[Haseeb Avarakkan]

Hello John,

Not so elegant version, but try this formula. Select H10:H23 then Conditional Format >> Format only cells contain > Cell Value > Equal to:

=DAY(SMALL(DATE(YEAR(I$3),MONTH(I$3)+(MAX(H$10:H$23)<=DAY(I$3)),H$10:H$23),1+SUMPRODUCT((DATE(YEAR(I$3),MONTH(I$3)+(MAX(H$10:H$23)<=DAY(I$3)),H$10:H$23)<=I$3)+0)))

[John19]

after the event on 28 of every month, the is nothing until 2 on the following month, so these 2 are constants, the first and last of the month.

Actually, can't we say something like if>28 then the next is always 2?

[Haseeb Avarakkan]

So, if you have fixed values, you can try in conditional format >> cell value > equal to:

=SMALL(H$10:H$23,1+COUNTIF(H$10:H$23,"<="&IF(DAY(I$3)>=28,1,DAY(I$3))))

[John19]

I don't know how this works but it sure works! perfect! thanks a million!

ITS SOLVED

[FDibbins]

Nice 1, Haseeb, I was overthinking this

[John19]

So, if you have fixed values, you can try in conditional format >> cell value > equal to:

=SMALL(H$10:H$23,1+COUNTIF(H$10:H$23,"<="&IF(DAY(I$3)>=28,1,DAY(I$3))))

i tested both, but this is the sure one

=DAY(SMALL(DATE(YEAR(I$3),MONTH(I$3)+(MAX(H$10:H$23)<=DAY(I$3)),H$10:H$23),1+SUMPRODUCT((DATE(YEAR(I$3),MONTH(I$3)+(MAX(H$10:H$23)<=DAY(I$3)),H$10:H$23)<=I$3)+0)))

[John19]

I want to thank you both I really appreciate your time and effort.

cheers