Register

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
Moderator

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

Create another line item formatted as TimePeriod - Weeks

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

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

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.

Thanks,

Sorna Raja Prabhu

Sorna Raja Prabhu
Moderator

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

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

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

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

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

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

## 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.

Thanks,

Contributor