Underline the last of each cell with conditional formatting

[DarkoDeign2]

Hi,

I have a column $AI2:$AI1500 in this column I have ISO-week numbers (e.g. wk22)
In the next column $AJ2:$AJ1500 I have days of the week (e.g. Monday... Sunday). It can be multiple rows with the same days.

I want to a conditional format the last Sunday in every week in column AI.
What suggestions do you have for such a formula?

 

[DanteAmor]

Try this:

Dante Amor
	AI	AJ
1	Week no	Days
2	28	Friday
3	28	Friday
4	28	Saturday
5	28	Saturday
6	29	Sunday
7	29	Sunday
8	29	Sunday
9	29	Monday
10	29	Monday
11	29	Tuesday
12	29	Tuesday
13	29	Friday
14	29	Saturday
15	29	Saturday
16	30	Sunday
17	30	Sunday
18	30	Sunday
19	30	Monday
20	30	Monday
21	30	Monday
22	30	Thursday
23	30	Friday
24	30	Saturday
25	31	Sunday
26	31	Sunday
27	31	Sunday
28	31	Monday
29	31	Tuesday
30	31	Wednesday
31
Hoja1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AJ1:AJ10000	Expression	=(AJ1="Sunday")*(COUNTIFS(AI2:AI$10000,AI1,AJ2:AJ$10000,AJ1)=0)	text	NO

In my example I am starting the conditional formatting in row 1, in cell AJ1, and the COUNTIFS function starts in the next row AI2 and AJ2.

=(AJ1="Sunday")*(COUNTIFS(AI2:AI$9987,AI1,AJ2:AJ$9987,AJ1)=0)

If you are going to change the numbering then you should consider it.
Also applies to starts in cell AJ1.
I gave the limit of 10000 as an example, but you can adjust it to the maximum number of rows that you are going to use.

Note: I put the conditional formatting with highlight in yellow, just to make it more noticeable, but you can change it to underline

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[DarkoDeign2]

Hi,

I don't know why but somehow this is not working for me. All I did was to replace the commas with semi-colons.
Instead of Sunday, Monday is highlighted...

 

[Peter_SSs]

I don't know why but somehow this is not working for me. All I did was to replace the commas with semi-colons.
Instead of Sunday, Monday is highlighted...

Then most likely you (understandably) had row 2 as the active cell row when you applied the CF.

Here is another one you could try. It expects that you would have AI2 as the Active Cell when applying the CF

23 07 15.xlsm
	AI	AJ
1	Week no	Days
2	28	Friday
3	28	Friday
4	28	Saturday
5	28	Saturday
6	29	Sunday
7	29	Sunday
8	29	Sunday
9	29	Monday
10	29	Monday
11	29	Tuesday
12	29	Tuesday
13	29	Friday
14	29	Saturday
15	29	Saturday
16	30	Sunday
17	30	Sunday
18	30	Sunday
19	30	Monday
20	30	Monday
21	30	Monday
22	30	Thursday
23	30	Friday
24	30	Saturday
25	31	Sunday
26	31	Sunday
27	31	Sunday
28	31	Monday
29	31	Tuesday
30	31	Wednesday
CF Last

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI2:AJ30	Expression	=AND($AJ2="Sunday",$AJ3<>"Sunday")	text	NO

If it is possible that you could have 2 adjacent rows with Sunday but from different weeks (as in rows 7 & 8 below) then you would need a further check in the formula.

23 07 15.xlsm
	AI	AJ
1	Week no	Days
2	28	Friday
3	28	Friday
4	28	Saturday
5	28	Saturday
6	29	Sunday
7	29	Sunday
8	30	Sunday
9	30	Sunday
10	30	Tuesday
11	30	Wednesday
12	30	Thursday
13	30	Friday
14	30	Saturday
15	31	Sunday
16	31	Monday
CF Last (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI2:AJ16	Expression	=AND($AJ2="Sunday",$AI3&$AJ3<>$AI2&$AJ2)	text	NO

 

[DarkoDeign2]

Hi,

Thank you for the suggestion.
What do you mean with
"if it is possible that you could have 2 adjacent rows with Sunday but from different weeks (as in rows 7 & 8 below) then you would need a further check in the formula."?
Do you mean that I should use the latter formula or do you mean that I need to use both formulas?

 

[Peter_SSs]

Do you mean that I should use the latter formula

That is what I mean.

You can see in my second mini sheet that I have just used one formula and that sheet still highlights the last Sundays of weeks 29, 30 and 31 which I think is what you want?

If the formula from my first mini sheet is used with the data from the second mini sheet then the last Sunday of week 29 would get missed - see below

23 07 15.xlsm
	AH	AI	AJ
1		Week no	Days
2		28	Friday
3		28	Friday
4		28	Saturday
5		28	Saturday
6		29	Sunday
7	This row is missed -->	29	Sunday
8		30	Sunday
9		30	Sunday
10		30	Tuesday
11		30	Wednesday
12		30	Thursday
13		30	Friday
14		30	Saturday
15		31	Sunday
16		31	Monday
CF Last (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AI2:AJ16	Expression	=AND($AJ2="Sunday",$AJ3<>"Sunday")	text	NO

 

[DarkoDeign2]

Thank you, Works perfectly now. Hade som problems earlier.

 

[Peter_SSs]

You're welcome. Glad we were able to help. Thanks for the follow-up.

 

[DarkoDeign2]

As the next step I was thinking column "AT". To have a formula that adds the numbers in column "AR" that correlates to the same week.
Here is my formula but it is not working.. It is something wrong with the "SUMIF".
=IF(AND($AL2="Sunday";$AK3&$AL3<>$AK2&$AL2);SUMIF($AK$2:$AK$1500;$AK2;$AR$2:$AR$1500))

 

[Peter_SSs]

Looks pretty close. It appears to just be missing what to return on all the other rows. Of course mine has commas where your will apparently have semicolons.

DarkoDeign2.xlsm
	AK	AL						AR		AT
1	Week no	Days
2	wk28	Friday						1
3	wk28	Friday						2
4	wk28	Saturday						3
5	wk28	Saturday						4
6	wk29	Sunday						5
7	wk29	Sunday						6
8	wk29	Sunday						7		95
9	wk29	Monday						8
10	wk29	Monday						9
11	wk29	Tuesday						10
12	wk29	Tuesday						11
13	wk29	Friday						12
14	wk29	Saturday						13
15	wk29	Saturday						14
16	wk30	Sunday						15
17	wk30	Sunday						16
18	wk30	Sunday						17		171
19	wk30	Monday						18
20	wk30	Monday						19
21	wk30	Monday						20
22	wk30	Thursday						21
23	wk30	Friday						22
24	wk30	Saturday						23
25	wk31	Sunday						24
26	wk31	Sunday						25
27	wk31	Sunday						26		159
28	wk31	Monday						27
29	wk31	Tuesday						28
30	wk31	Wednesday						29
CF Last

Cell Formulas
Range	Formula
AT2:AT30	AT2	=IF(AND($AL2="Sunday",$AK3&$AL3<>$AK2&$AL2),SUMIF($AK$2:$AK$1500,$AK2,$AR$2:$AR$1500),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AK2:AL30	Expression	=AND($AL2="Sunday",$AL3<>"Sunday")	text	NO