How to highlight /mark every 4th Monday in a year

thanveer · #1 11-21-2024, 10:39 PM

Dear friends, kindly help me in this.

I have all dates in a year in one column
1. I want to highlight every 4th Monday recurring (irrespective of the month)
2. Highlight every alternate Friday (irrespective of month)

Kindly help me frame the formula

Thanks in advance.

ArviLaanemets · #2 11-22-2024, 01:28 AM

Let's assume your dates are in column A, with header in A2.

On fly, into some column in row 2 enter the formula like:

Code:

=IIF(MONTH(DATE(YEAR($A2),MONTH($A2),1) + WEEKDAY(DATE(YEAR($A2),MONTH($A2),1),2)-1) + 21) = MONTH($A2),DATE(YEAR($A2),MONTH($A2),1) + WEEKDAY(DATE(YEAR($A2),MONTH($A2),1),2)-1) + 1,DATE(YEAR($A2),MONTH($A2),1) + (WEEKDAY(DATE(YEAR($A2),MONTH($A2),1),2)-1) + 18)

, and copy it down.

Alternatively you can define some dynamic Name, and use it in same formula, like:
activate any cell in row 2;
Define Name e.g.

Code:

nFirstMonday = DATE(YEAR($A2),MONTH($A2),1) + (WEEKDAY(DATE(YEAR($A2),MONTH($A2),1),2)-1)

Your formula will be now like

Code:

=IIF(MONTH(nFirstMonday + 21) = MONTH(MONTH($A2), nFirstMonday + 21, nFirstMonday + 18)

Now you can use this calculated column as source for conditional formatting, for filtering data, or for whatever.

p45cal · #3 11-22-2024, 05:18 AM

Another (lazy) way:
Find one of the Mondays you want highlighting, say, for example, it's in cell C10. Then in any other cell enter this formula:
=MOD(C10,28)
The 28 is 28 days=4 weeks = every 4th Monday.
Make a note of the result (Excel will probably auto-format the result to a date, you'll need to reset the format to General). You can now remove this formula. From the picture below you'll see I got the answer 23.
Now set up the conditional formatting as shown in the picture (the second CF, with the red font).

2024-11-22_120832.jpg

I also did the fortnightly Fridays where I got the answer 6 to the Friday I chose with
=MOD(C7,14)
being the first CF in the picture with green font. 14 is two weeks (every alternate Friday).

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Pivot table group by year . Define year start and end	maw444	Excel	2	12-17-2023 01:04 AM
How to search for a specific year from a table with year range?	Wii	Excel	0	05-05-2015 12:40 PM
How to calculate a rolling year-to-date percentage by quarter as the year progresses	sleake	Excel Programming	2	04-23-2015 11:51 AM
Creative Ways for a year-to-year comparison???	ridonkulous5	Excel	1	03-23-2011 04:49 PM
find - reading highlight - highlight all / highlight doesn't stick when saved	bobk544	Word	3	04-15-2009 03:31 PM