## how to calculate WEEKNUMBER of a particular date in a year 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   Community Boss

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.

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
HeathcoteAndHerran.com
18 REPLIES 18  Moderator

Create another line item formatted as TimePeriod - Weeks

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

Hi Mishab,

Model Calendar is set to Calendar/Months/Years.

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

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.  Thanks,

Sorna Raja Prabhu

Sorna Raja Prabhu  Moderator

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

Hi Sorna,

Model Calendar is set to Calendar/Months/Years.

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

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 Step 2:

in Your Main Module create 4 Line items. 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) easy logic -> let me know if u didn't understand.

Thanks,

Sorna Raja Prabhu

Sorna Raja Prabhu Frequent Contributor

Hi @akprasa1 ,

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