ShowAll Questionssorted byDate Posted
forceDotMom

# Week of The Year - Week of the Quarter Formula

Working on a formula to ultimately get the week number of the quarter, and figured I would need to start with the week of the year.  I have found a few samples here on the boards, but I am not completely clear on how they work, and I am still having issues with the last week of the year.

Here is my Week Number Formula:

`MOD(FLOOR( ( Test_Date__c -DATEVALUE("2006-01-01" ))/7),52)`

Here is my Week in the Quarter Formula:

`WeekNumber__c - CASE(Current_Quarter__c , 1, 0, 2, 13, 3, 26, 4, 39, 0)`

Here is the Current Quarter Formula I am referencing in my Week of the Quarter Formula:

`CEILING(MOD(FLOOR( ( Test_Date__c -DATEVALUE("2006-01-01" ))/7),52)/13)`

Eventually I am going to have to compile all of these functions into one big formula, but for now I have them broken out.  Looking forward to this discussion!

-Brandy

forceDotMom

I guess I didnt state my issue very clearly.... so the Last week of the year comes up as week 0, or Week 53.

I don't really understand the base date of 1/1/2006, but I found that over here: Other Post

Thanks!

-Brandy

bob17

You must first decide how you want to define "week of the year".  Per the

ISO-8601 standard:

-  Weeks starting on Monday.
- The first week of the year is the week that contains that year's first Thursday.
- The highest week number in a year is either 52 or 53.

The formula referenced in the other post by MRietveld does not strictly adhear to the ISO-8601 standard (may or may not be a big deal for you) as it does not properly account for the rule that the first week is the first to contain a Thursday (i.e. 1/4/2009 should be week 1, not week 2).

This sample formula calculates the week of the year for the Opportunity Close Date,
relplace CloseDate with the date field on which you want to calculate
the week of the year or use TODAY() to get the current week of the year.
A value of 99 represents an error which should never occur but need some value

for the CASE else.

Case(
MOD( DATE(YEAR(CloseDate), 1, 1) - DATE (1900, 1, 7), 7),
0, ( CloseDate  - DATE(YEAR(CloseDate),1,1) + 3) /7,
1, ( CloseDate  - DATE(YEAR(CloseDate),1,1) + 4) /7,
2, ( CloseDate  - DATE(YEAR(CloseDate),1,1) + 5) /7,
3, ( CloseDate  - DATE(YEAR(CloseDate),1,1) + 6) /7,
4, ( CloseDate  - DATE(YEAR(CloseDate),1,1) + 7) /7,
5, ( CloseDate  - DATE(YEAR(CloseDate),1,1) + 1) /7,
6, ( CloseDate  - DATE(YEAR(CloseDate),1,1) + 2) /7,
99)

If the first day of the year of the test date is a Friday, Saturday, or Sunday and the date being
tested is the 1st, 2nd, or 3rd of January the week will equal 0. Additional code
could be added to check for weeks 0 or 53 if you wanted to limit results to the
more traditional 1-52 but your likely to run into Salesforce compiled formula
size limits (good luck figuring where those numbers come from).

A nice test site for checking results is http://whatweekisit.com

DanP

When I use this formula and set it up as a DATE formula, when I check the syntax is is coing back and saying the results are a number, incompatible with DATE??  Strange as I sdet it up as a date as noted!

bob17

Sorry that was not clear.  The formual field needs to be defined as a Number result because it returns a numeric value representing which week in a calendar year the date occurs in.  The date field that I referenced is the field you want to use to determine which week of the year.  My example used the CloseDate of an opportunity but you may want to use the formula to determine the week number for a different date.

DanP

Thank you, I did figure this out.  This is the correct way to do this.  Unfortunately, this creates a huge numnber of characters.  I am trying to create a cash flow report with a possibility of 3 payment values that could exist in one week.  The only way I can figure how to do this is to create IF statements allowing any of Payments 1, 2, 3 to appear in a week column.  There are just too many characters!  If you have any ideas, let me know.

Case(
MOD( DATE( YEAR( Payment_1_Date__c ), 1, 1) - DATE (1900, 1, 7), 7),

0, (  Payment_1_Date__c   - DATE( YEAR( Payment_1_Date__c ),1,1) + 3) /7,
1, (  Payment_1_Date__c - DATE(YEAR(Payment_1_Date__c),1,1) + 4) /7,
2, (  Payment_1_Date__c - DATE(YEAR(Payment_1_Date__c),1,1) + 5) /7,
3, (  Payment_1_Date__c - DATE(YEAR(Payment_1_Date__c),1,1) + 6) /7,
4, ( Payment_1_Date__c  - DATE(YEAR(Payment_1_Date__c),1,1) + 7) /7,
5, ( Payment_1_Date__c  - DATE(YEAR(Payment_1_Date__c),1,1) + 1) /7,
6, ( Payment_1_Date__c  - DATE(YEAR(Payment_1_Date__c),1,1) + 2) /7,
0)

Ignacio Jimenez

Hi, can you share  how you designed your cashflow report? Thanks!