• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Alternatives to WEEKNUM

D

deeetour

New Member
Hey all,


In one of the dashboards I've built, I use simple conditional formatting to show upcoming invoice due dates in different colours, grouped by which week the fall due in. For example:


WEEKNUM(DueDate) < WEEKNUM(TODAY()) : Red

WEEKNUM(DueDate) = WEEKNUM(TODAY()) : Orange

WEEKNUM(DueDate) = WEEKNUM(TODAY())+1 : Yellow

WEEKNUM(DueDate) = WEEKNUM(TODAY())+2 : Green


This creates a nice colour coded list showing the next 4 weeks worth of due invoices. Now that we are approaching the end of the calendar year, the problems with WEEKNUM are becoming apparent. I have 2 invoices in the list which are due in the 2nd week of January 2013, which WEEKNUM returns as 2, and thus is showing Red (overdue).


How would you best tackle this?
 
Hi ,


You need to use both the week number , and the year ; if the Year(DueDate) <= Year(TODAY()) and if the WEEKNUM(DueDate) < WEEKNUM(TODAY()) then RED , and so on for the other colors.


Narayan
 
You must log in or register to reply here.
Back
Top