Weeknum Function

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
148
I have an Excel 2010 Spreadsheet which contains a number of random dates in column P. What I am trying to do is highlight the row if the date is within 4 weeks of the current date, I would also like to highlight the dates in a different colour if the date is within 3 weeks of the current date.

I believe the best formula to use would be to use the Weeknum Function. Not having used this formula previously would appreciate it if someone could help with a conditional format or some VBA which would achieve the desired outcome. TIA
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
Hi Chris,

would it be acceptable to set the criteria as follows:set this for the entire row (assuming your data starts from row 1 downwards. Adjust as needed)

$P1<(Today()+28)
For the second question, I'd go with

P1<(Today()+21)

Make sure that the last rule has higher priority in the conditional format overview.
 

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
148
Hi Noodleski,

Thanks for your prompt reply, I don't think your suggestion will work as needed. The reason is that if I run this report on a Monday, the formula will only pick up items that are exactly four weeks old rather than highlighting ALL the items for that week that will be four weeks old and the similar problem will exist for the items that are three weeks old. Hence the reason why I suggested the Weeknum formula. Thanks again.
 

Noodleski

Active Member
Joined
Nov 18, 2015
Messages
467
Hi Noodleski,

Thanks for your prompt reply, I don't think your suggestion will work as needed. The reason is that if I run this report on a Monday, the formula will only pick up items that are exactly four weeks old rather than highlighting ALL the items for that week that will be four weeks old and the similar problem will exist for the items that are three weeks old. Hence the reason why I suggested the Weeknum formula. Thanks again.
Fair enough. Not a native speaker, I missed that nuance.
Try
week($P1)<(week(today())+4)

and
week(P1)<week(today())+3)< html=""></week(today())+3)<>
 
Last edited:

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You'll have problems with WEEKNUM if your date is near the beginning of the year. Maybe try eg:

=(P1-WEEKDAY(P1,1)+1)>=((TODAY()-28)-WEEKDAY(TODAY(),1)+1)
 

Chris Waller

Board Regular
Joined
Jan 18, 2009
Messages
148
Andrew,

Thanks for that, but it still doesn't appear to work. It may be me, but when I tried this on my Spreadsheet the formula appears to gives False when the date is in the past. What I am trying to do is when a date is upcoming I need to take action at 4 weeks prior to the date in column P. I then need to know when it is three weeks prior to the date in column P, but after that I'm not interested as the action should have been taken at these two points in time. TIA
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I think the first suggestion by Noodleski is the way to go.

Thanks for your prompt reply, I don't think your suggestion will work as needed. The reason is that if I run this report on a Monday, the formula will only pick up items that are exactly four weeks old
That's not a correct assessment of what that formula will do.

It will ADD 21 or 28 days (3 or 4 weeks) to the current date.
If the Date in P1 is PRIOR to that date, the formula will be TRUE
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,802
Messages
5,470,864
Members
406,732
Latest member
Micka77

This Week's Hot Topics

Top