Format pins based on weeks in the future

[Phil_Waite]

Hi,

I have pins on a map formatted with different colours to show which orders are for this week, next week, week after and the week after that.

AND(OR([Order Status]=“Open”,[Order Status]=“Exported to Xero”),[Delivery Date]>(TODAY()-1),WEEKNUM([delivery Date])=(WEEKNUM(TODAY())+3))

This works fine until we reach the end of the year as we are in week 51 but the delivery date for an order is 09/01/2019 which is week 2. The expression is looking to see if the delivery date is in week 54.

Does anyone know how to solve this.

I have used the below expression but I will have to set up far too many formats to cover all the combinations of what week out of the last 4 of the year we are currently in and what week out of the 4 at the begginning of the next year the delivery date is in.

AND(OR([Order Status]=“Open”,[Order Status]=“Exported to Xero”),[Delivery Date]>(TODAY()-1),WEEKNUM([delivery Date])=WEEKNUM(“01/09/2020”),WEEKNUM(Today())=WEEKNUM(“12/17/2019”))

Many thanks

Phil

[Arthur_Rallu · Accepted solution]

Hi Phil,
You can probably try an expression like this one to compute the number of weeks out until delivery:

FLOOR(TOTALHOURS([Date]-(TODAY()-WEEKDAY(TODAY())))/168.0)

There might be simpler but this one works at least. It’s based on the time between now and the due date, so it won’t have issues with week number (in the year) being reset.
Note: it works as expected for week days (Monday through Friday), but you will need to tweak it a little to get what your business needs for Saturday and Sunday.

[Phil_Waite]

Thanks

this worked fine

Phil

[Aleksi]

@Arthur_Rallu Good example for a new expression like EOWEEK()