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

PrevContributor
PrevContributor
in Modeling

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. 

Tagged:

Best Answer

  • PrevContributor
    PrevContributor
    Answer ✓
    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.

Answers

  • nathan_rudman
    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
    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. 
  • nathan_rudman
    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.

Categories