function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
CardonaCardona 

How to calculate week number in a 53 week year

Hello,

 

I am trying to gather the week number of a week within a fiscal year. I have been able to calculate this for the standard 52-week year using the following formula:

 

MOD(FLOOR( ( CloseDate -DATEVALUE("YYYY-MM-DD"))/7),52)+1

 

My issue is that i need to now calculate the fiscal week for a fiscal year that has 53 weeks. 

 

Thanks for the help.

Best Answer chosen by Admin (Salesforce Developers) 
CardonaCardona

After a bit more playing, here is the formula to calculate 53 weeks.

 

MOD(FLOOR( ( CloseDate  -DATEVALUE("YYYY-MM-DD" ))/7)-48,53)+1

 

Keeping in mind that the date value information is the first day of your fiscal year.

 

If the first day of your Fiscal Year is September first and your company beings its weeks on Fridays, then the DATEVALUE you should use is DATEVALUE("2006,09,01) becuase September 1, 2006 falls on a Friday.

All Answers

CardonaCardona

After a bit more playing, here is the formula to calculate 53 weeks.

 

MOD(FLOOR( ( CloseDate  -DATEVALUE("YYYY-MM-DD" ))/7)-48,53)+1

 

Keeping in mind that the date value information is the first day of your fiscal year.

 

If the first day of your Fiscal Year is September first and your company beings its weeks on Fridays, then the DATEVALUE you should use is DATEVALUE("2006,09,01) becuase September 1, 2006 falls on a Friday.

This was selected as the best answer
GulbargaGulbarga

I have 2 doubts:

 

1. Why did you subtract it by 48?

2. Why did you add 1?

 

 

I am a beginner. Please explain?
CardonaCardona

The 1 is to increase the week number itself. Without it, you will simply start off at 0 and not increate on the week.

 

When using this formula, its starts you off at week 49 through the math and also having the 53 for the number of weeks.

 

by subtracting the 48, it allows you to start at 1 and continue the week counting from there.

 

i recommend you throw it into a field and run a report on the year that you have 53 weeks. summarize first by the close date by fiscal week and then place your custom field as the second summary to validate the formula.

 

if this is incorrect, please let me know and any advice that you may have.

 

Close Date: FY 2011 (100,000 records)   

     Close Date: Week 1 FY 2011 (10,000 records)         

          Fiscal Week: 1 (10,000 records) 

 

.....

 

     Close Date: Week 53 FY 2011 (7,931 records)        

          Fiscal Week: 53 (7,931 records) 
Davide GammoneDavide Gammone
I have post an Idea for that problem!

Formula Function ISOWEEKNUM

https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ

Thanks
Davide