how to calculate WEEKNUMBER of a particular date in a year

akprasa1
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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHeathcote
Community Boss

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

Hi @akprasa1 

Given you model calendar restraints I would recommend using the following approach;

 

Create a 'Time Properties - Year' module containing only time dimension set to Year. Add a single line item called, 'Start Date.'

Type the following formula - START()

This will populate the line item with the date of the first day of each calendar year.

 

Create a 'Time Properties - Day' module containing only time dimension but this time set to Day. 

Add the following line items;

1. Date, format date, formula START()

1. WeekDay, format number, formula WEEKDAY(Date, 1) The '1' indicates the start of the week, Mon =1, Tue=2 etc

2. NewWeek, format number, formula IF WeekDay = 1 OR NewYear THEN 1 ELSE 0

3. NewYear, format boolean, formula Date = YEARVALUE('Time Properties - Year'.Start Date)

4. WeekNumber, format number, formula CUMULATE(New Week, NewYear)

 

Set all summary options to none.

The resulting table can be used to LOOKUP the week number for any given date within the model calendar depending on which day of the week each week starts. Adjust the WEEKDAY function to change this. 

 

 

I have attached some screenshots to help you see what I have suggested.

 

Good luck,

 

Chris

 

 

 

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA

View solution in original post

18 REPLIES 18
Misbah
Moderator

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

 

akprasa1
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

prabhu
Community Boss

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
Misbah
Moderator

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

@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

prabhu
Community Boss

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

Thank you Misbah, for the suggestion.
Sorna Raja Prabhu
akprasa1
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

prabhu
Community Boss

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
naushad786
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

 

akprasa1
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