Weeknum Function

[Chris Waller]

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

 

[Noodleski]

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]

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]

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)<>

 

[Andrew Poulsom]

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]

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]

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