Formula to convert a Date to the Week number of the year or quarter

I am trying to find a way to take a date and convert the date to a number that would represent the week number in a calender year or the week number in a calendar quarter. The value could either bring back the week based on a the calendar week or the actual number of days divided by 7 (for example January 5, 2015 is a Monday, but it is in the second week of January, so the result could be week 2 or if the weeks are by actual days, it would be week 1). I have a list of data that includes several years worth of transactions. I would like the logic to work regardless of the year of the transaction. Every year would have a week 1, 2, 3, etc.

Accepted answers

PrevContributor

While not perfect/accurate (I concur w prior post that adjusting model time scale is most correct manner) - simple math as follows for your second scenario :

a. ROUND((DataDate - DATE(YEAR(DataDate), 1, 1)) / 7, 0, DOWN) + 1 : week one beginning on January 1st, week two beginning on January 8th, and week 53 having only one or two days (for leap years)

If 53rd week is not an option - could "round off" those one (or two) extra days into the year
b. : ROUND((DataDate - DATE(YEAR(DataDate), 1, 1)) / DAYSINYEAR(YEAR(DataDate)) * 52, 0, UP)

While b. is "inaccurate" with one of more periods w/ > 7 days, I believe it might be consistently inaccurate across (non-leap) years and does works across all years irrespective of defined periods in model.

I would view both as potential 'quick fix' options until you are willing to build in day/weekly time scale or additional modules.

All comments

nathan_rudman

Hello,

The calendar type set up in your application is very important to define what can be done with week/day and time functions.
Do you have one already fixed ? if yes what is it ?

PrevContributor

The calendar is set to Months/Quarters/Years as that is the time structure for our financial calendar. Looking for a function or formula that will take a date in a line of data in a module that is not using time as a deminsion and bring back the week in the that date falls into.

PrevContributor

nathan_rudman

unless you are willing to enter some parameters yourself (such as for every year, which day is the 1 of january), I don't think it's currently possible. In the next version a function weekday() will be released: allowing you to know which # of the week is a specfiic date. With some calculations, that should do what you need.

Quick Links

Can you help?

Unanswered questions

How have you from-scratch built lot-level, geo-based Inventory Consumption?
We’re working on modeling inventory consumption at the lot level, accounting for shelf life considerations, since we need visibility into which lots may not consume—both for risk reporting and E&O analysis. Each item may have customers with different shelf life requirements. For example, item A may need 270 days for some…
How to bring Row data into Columns?
Hi, Can you help me, how can we get row data into columns using Unique ID? Per attached below first snapshot, I want to bring rountingOrder 4, RecipientName, Sent and Completed dates in 1 row as show in sanpshot 2. Do I need to create Hierarchy list and Map?
How to Design Simulation Planning in Anaplan with Multi-Level User Input and Step-by-Step Logging?
Hi Anaplan Community, I'm working on a simulation planning model where users need the flexibility to input pricing adjustments at any level of the product hierarchy, and I’d love your guidance on best practices. 📌 Scenario Overview: We have a 4-level product hierarchy: Brand Category Sub Category SKU (lowest level) The…