Highlight only sunday of current week

papil

New Member
Joined
Aug 18, 2016
Messages
14
i am looking for way to highlight only sunday of current week. every time i open the excel it should only highlight that way.
can someone help with the same. thanks. data looks like below-
weekdate
12/11/2018
22/18/2018
32/25/2018
43/4/2018
53/11/2018
63/18/2018
73/25/2018
84/1/2018
94/8/2018
104/15/2018
114/22/2018
124/29/2018
135/6/2018
145/13/2018
155/20/2018
165/27/2018
176/3/2018
186/10/2018
196/17/2018
206/24/2018
217/1/2018
227/8/2018
237/15/2018
247/22/2018
257/29/2018
268/5/2018
278/12/2018
288/19/2018
298/26/2018
309/2/2018
319/9/2018
329/16/2018
339/23/2018
349/30/2018
3510/7/2018
3610/14/2018
3710/21/2018
3810/28/2018
3911/4/2018
4011/11/2018
4111/18/2018

<colgroup><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
So it looks like all your dates are already only Sundays. So all we need to do is check to see if the week number of the date matches the week number of the current date.
We can do that pretty easily with Conditional Formatting.

Assuming your dates are in column B, select column B, and apply this Conditional Formatting formula:
Code:
=weeknum(B1)=weeknum(today())
and choose your desired highlighting color
 
Upvote 0
Hi papil,

Assuming that your dates are in Sheet1, column A (starting from row 1), you can start by defining a new Name, e.g. "rngSunday"
=IFERROR(MATCH(TODAY(),Sheet1!$A:$A,0),MATCH(TODAY(),Sheet1!$A:$A,1)+1)

It will return the correct row number for the exact date (if "today" is Sunday), or the approximate date (if "today" is not Sunday).

Select the whole column (e.g. column A) and go to Conditional Formatting > New Rule > Use a foruma to determine which cells to format. Enter the following formula:
=ROW(A1)=rngSunday

Let me know if that works for you.
 
Upvote 0
JustynaMK,

That seems like overkill. Did you see my solution above?

Even if they did have a situation where they have non-Sunday dates in their list, that could be handled with a minor change to my CF formula, i.e.
Code:
=AND(WEEKDAY(B1)=1,WEEKNUM(B1)=WEEKNUM(TODAY()))
 
Upvote 0
Or, assuming dates in B2:B42:

Highlight the range, starting at B2.
Go to conditional formatting > Use a formula to determine which cells to format
Formula: =B2=CEILING(TODAY()-1,7)+1
Click Format > choose desired format
Hit OK
 
Upvote 0
Or, assuming dates in B2:B42:

Highlight the range, starting at B2.
Go to conditional formatting > Use a formula to determine which cells to format
Formula: =B2=CEILING(TODAY()-1,7)+1
Click Format > choose desired format
Hit OK


This works .Thanks
 
Last edited:
Upvote 0
=AND(WEEKDAY(B1)=1,WEEKNUM(B1)=WEEKNUM(TODAY())) this is not working for me.
If your data truly looks like you posted in your original post, I do not see why it wouldn't work.
Did you highlight the whole column, or just specific rows in the column?

Note that when applying Conditional Formatting to multiple cells at once, you need to write the CF formula as it pertains to the very first cell in the range you have selected, and Excel will automatically adjust it for all other cells. I instructed to select the WHOLE column B, so cell B1 would be the first cell in that selection (doesn't matter if there is a header in row 1, it won't meet the CF criteria). Jon's solution tells you to select B2:B42, so his formula references B2 (the first cell in his selection).
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

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