how to calculate WEEKNUMBER of a particular date in a year

Highlighted
Contributor

how to calculate WEEKNUMBER of a particular date in a year

Hi Anaplanners,

 

i am stuck with one the calculation ,which requires me to calculate WEEK NUMBER of a date in the year.In excel we can use WEEKNUM Function to get the number of the week .

eg,WEEKNUM(2020-03-02) = 10

 

How can i achieve this

 

 

 

18 REPLIES 18
Highlighted
Master Anaplanner/Community Boss

Re: how to calculate WEEKNUMBER of a particular date in a year

@akprasa1 

 

Create another line item formatted as TimePeriod - Weeks

 

Formula = PERIOD(Date) - Here Date is the Name of the First line Item

 

Misbah_0-1585299786889.png

 

Highlighted
Contributor

Re: how to calculate WEEKNUMBER of a particular date in a year

Hi Mishab,

 

Model Calendar is set to Calendar/Months/Years.

i will not be able to format the line items as time period - Week

Highlighted
Super Contributor

Re: how to calculate WEEKNUMBER of a particular date in a year

Hi @akprasa1,

 

You can achieve this using PERIOD() Function

 

Step 1: Line item - Date(Date Format), Week Number (Time Period - Week Format), and a number formatted line item.

 

Capture.JPG

 

 

Capture1.JPG

 

 

Thanks,

Sorna Raja Prabhu

Sorna Raja Prabhu
Highlighted
Master Anaplanner/Community Boss

Re: how to calculate WEEKNUMBER of a particular date in a year

@Sorna Raja Prabhu 

 

Please do not clutter the formula.

 

VALUE(MID(NAME 

Also do not hardcode the parameters like 6 here. There are tons of ways to make it dynamic esp using FIND function. 

 

If you really need the Number only in Weeks then try and break the formula into multiple pieces/Line items. You will surely see a performance gain overall. Let's not neglect these small things, it takes a toll on model performance when the model scales up. Stick to Basics, take one function at a time in system modules.

 

Happy Modelling

Highlighted
Super Contributor

Re: how to calculate WEEKNUMBER of a particular date in a year

Thank you Misbah, for the suggestion.
Sorna Raja Prabhu
Highlighted
Contributor

Re: how to calculate WEEKNUMBER of a particular date in a year

 Hi Sorna,

Model Calendar is set to Calendar/Months/Years.

i will not be able to format the line items as time period - Week

Highlighted
Super Contributor

Re: how to calculate WEEKNUMBER of a particular date in a year

Hi @akprasa1,

 

I have another approach,

 

Step 1 : Create a System Module with 1 line item to manually enter the Calendar year's Starting date.

Eg; 29th December 2019 as First Staring day for calendar year 2020

 

Capture.JPG

 

 

Step 2:

in Your Main Module create 4 Line items.

 

Capture1.JPG

 

Formulas are :

Today's Date : Manually enter the date, which you have to calculate the week for.

Total Days : 'Today''s Date' - System Module.First Week Starting Date (To Calculate number of days between Today's Date and Year Starting Date)

Remainder MOD(Total Days, 7) 

Week NumberIF Total Days = 0 AND Remainder = 0 THEN 1 ELSE IF Total Days < 7 AND Remainder < 7 THEN 1 ELSE IF Total Days >= 7 AND Remainder <> 6 THEN ROUND(Total Days / 7, 0, DOWN) + 1 ELSE ROUND(Total Days / 7)

 

Capture2.JPG

 

 

 

easy logic -> let me know if u didn't understand.

 

Thanks,

Sorna Raja Prabhu

Sorna Raja Prabhu
Highlighted
Frequent Contributor

Re: how to calculate WEEKNUMBER of a particular date in a year

Hi @akprasa1 ,

 

Firstly, you need to check Week format as Numbered in Time Setting. if it is not then set as Numbered.

 

naushad786_0-1585301364619.png

naushad786_1-1585301397725.png

Hope, it will help you,

 

Thanks,

Naushad

 

Highlighted
Contributor

Re: how to calculate WEEKNUMBER of a particular date in a year

Hi Naushad,

 

Model Calendar is set to Calendar/Months/Years.

i will not be able to format the line items as time period - Week