function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
pbaezapbaeza 

ISO Week Calculation

In Sweden and some other parts of Europe the week numbering is often using the ISO 8601 standard that says that the first week of the year is the first week with at least 4 days in the new year. Some consequences of this is that the final dates of one year can be week 52, 53 or even week 1( in the following year). The same goes for the first dates of a year.

 

To be able to do weekly comparison report Year-to-Year with this weeknumbering it is necessary to create custom fields for the opportunity that calculates the Year and Week number for a given close date. Here is one solution using three custom formula fields.

 

Field: Global Sales Report Weekday

Description:  Day 1 = Sunday, 2 = Monday....., 7=Saturday

Formula:  MOD( CloseDate  - DATE(1900, 1, 7), 7)+1

 

Field: Global Sales Report Week Year

Formula: YEAR(CloseDate + (MOD(8- Global_Sales_Report_Weekday__c ,7)-3))

 

Field: Global Sales Report Week

Formula:

FLOOR((CloseDate - DATE( Global_Sales_Report_Week_Year__c ,1,1) +
MOD(
(MOD( DATE(Global_Sales_Report_Week_Year__c,1,1) - DATE(1900, 1, 7), 7)+1)
+1,7)-3) / 7 + 1)

 

Hope this can be of use for anyone else

 

Peter Baeza

InfoAction AB, Sweden

 

forceDotMomforceDotMom

This is pretty interesting.  I found it to work mostly, but I am a little confused.  When I have a date :  12/31/2008, the Global Report Year says 2009, and Global Report Sales Week says week 1.  Is that correct?

Peter.BaezaPeter.Baeza

Yes that is correct. the jan 1 2009 is a thursday so mon 29 to wed 31 2008 is in week 1 2009

/ Peter

eliotstock5eliotstock5

The above formula field for week number as Apex code:

 

public static Integer isoWeekNumber(Date value) {
		Integer daysSince1900_01_07 = Date.newInstance(1900, 1, 7).daysBetween(value);
		Integer dayNumber = Math.mod(daysSince1900_01_07, 7) + 1;
		
		Date dateForYear = value.addDays(Math.mod(8 - dayNumber, 7) - 3);
		Integer year = dateForYear.year();
		Date year_01_01 = Date.newInstance(year, 1, 1);
		
		Integer week = (Integer)Math.floor((year_01_01.daysBetween(value)
				+ Math.mod((Math.mod(Date.newInstance(1900, 1, 7).daysBetween(year_01_01), 7) + 1) + 1, 7) - 3) / 7 + 1);
				
		return week;
	}

 and some tests:

 

	@isTest
	public static void knownIsoWeekNumbersWorkAsExpected() {
		Date d;
		
		d = Date.newInstance(2012, 12, 30); // Sun
		System.assertEquals(52, isoWeekNumber(d));
		
		d = Date.newInstance(2012, 12, 31); // Mon
		System.assertEquals(1, isoWeekNumber(d));
		
		d = Date.newInstance(2013, 1, 1); // Tue
		System.assertEquals(1, isoWeekNumber(d));
		
		d = Date.newInstance(2013, 1, 2); // Wed
		System.assertEquals(1, isoWeekNumber(d));
		
		d = Date.newInstance(2013, 1, 3); // Thu
		System.assertEquals(1, isoWeekNumber(d));
		
		d = Date.newInstance(2013, 1, 4); // Fri
		System.assertEquals(1, isoWeekNumber(d));
		
		d = Date.newInstance(2013, 1, 5); // Sat
		System.assertEquals(1, isoWeekNumber(d));
		
		d = Date.newInstance(2013, 1, 6); // Sun
		System.assertEquals(1, isoWeekNumber(d));
		
		d = Date.newInstance(2013, 1, 7); // Mon
		System.assertEquals(2, isoWeekNumber(d));
		
		d = Date.newInstance(2013, 1, 8); // Tue
		System.assertEquals(2, isoWeekNumber(d));
	}

 Please post here if you spot anything wrong with this for other years. Thanks.

 

 

 

 

rklaassenrklaassen

Thanks eliotstock5, your method works like a charm!

Ken BKen B
@Peter.Baeza this is exactly what I was looking for. Thank you!

Ken BKen B
Is it possible to create a rolling 3 month report showing next 3 months compared to last year's same week numbers sales? I can't get any combination of report filters ot make it happen without showing every week in between.

Or is it possible to create a Viseualforce page that acocmplishes this?
Coral RacingCoral Racing
Hi What if my week began on a Friday, how would I adapt to suit this?
Thanks

Sonya
Luka CordasicLuka Cordasic
@eliotstock5
thank you!
Paul da SilvaPaul da Silva
Thanks a million @pbaeza it worked treat for me.  Much appreciated.
Daniel Khan 21Daniel Khan 21
How do I set up this APEX Code? Is it a Class / Trigger /etc. ? Thanks for any help!
@altius_rup@altius_rup
For future visitors, here is a formula version of @eliotstock5's Apex code :
 
(1 + 
    (
        CloseDate
        - DATE(YEAR((CloseDate - 3 + MOD(8 - (MOD(CloseDate - date(1900, 1, 7), 7)+1), 7))), 1, 1)
        + MOD(1 + 
            (1 +
                MOD(
                    DATE(YEAR((CloseDate - 3 + MOD(8 - (MOD(CloseDate - date(1900, 1, 7), 7)+1), 7))), 1, 1)  -  DATE(1900, 1, 7), 7
                )
            ), 7
        ) - 3
    ) / 7
)

​​​​​​​
 
Alexandr LuferovAlexandr Luferov
@altius_rup seems that it not works: at least for 2020-01-17 it return 4, but should be 3
Jack Collins 13Jack Collins 13
MORE ROBUST FORMULA HERE
Hi, 

I tried the above formula, and it was very helpful,but sometimes incorrect for certain years. 

After some experimenttion, I arrived at the formula, which can be used as a formula field. This works by calculating the date of the first Monday of the year and incrementing from there ( EU week numbers start on Mondays, not Sundays, but you can adjust the below formula to suit yourself).

Formula as follows, where mDate is the date field for which you seek to return the week number...

FLOOR((( mDate -(DATE(YEAR(mDate ),1,1)-MOD(DATE(YEAR(mDate ),1,1)-2-DATE(1900,1,1),7))+
MOD(
(MOD((DATE(YEAR(mDate ),1,1)-MOD(DATE(YEAR(mDate ),1,1)-2-DATE(1900,1,1),7))-DATE(1900,1,2),7)+1)
+1,7)-3)/7+1))

NOTE 1/2: MOD( DATE( YEAR(mDate), 1, 1)-2, 7) ##  returns the Monday of week 1 of the year that mDate is in

Note 2/2: The last week of december and the first week of January will be split between numbers 53 and 1. This is actually deslireabe to keep because A) it makes calculations such as finding the number of weeks between two dates accurate when one of those dates falls on week '53' B) any attempt to get around this seems to exceed the 5,000 characters for compiled code in the Salesforce formula field writer.
Mikkel Damgaard LorensenMikkel Damgaard Lorensen
This was the old Excel way of doing it before "ISOWeek" was invented.
Works great in salesforce as well.
FLOOR(
(DATEVALUE(Your_date_field) -
DATE(YEAR(DATEVALUE(Your_date_field) -
WEEKDAY(DATEVALUE(Your_date_field)-1)+4),1,3)+

WEEKDAY(DATE(YEAR(DATEVALUE(Your_date_field) -
WEEKDAY(DATEVALUE(Your_date_field)-1)+4),1,3))+5)/7
)

 
Davide GammoneDavide Gammone

I have post an Idea for that problem!

Formula Function ISOWEEKNUM (https://trailblazer.salesforce.com/ideaView?id=0874V0000003lvEQAQ" target="_blank)

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

Thanks
Davide

 

Laura Dix-BowlerLaura Dix-Bowler
@mikkel I've tried your formula but I keep receiving the error: Incorrect argument type for function 'DATEVALUE()'. (Related field: Formula)
Goku ZeusGoku Zeus
Growth 10-pound cannabis plants is a difficult endeavour that involves careful consideration of various elements, including as strain selection, growth conditions, and cultivation procedures.
https://justcannabisseed.com/grow-a-ten-pound-ten-foot-tall-cannabis-marijuana-plant/


Max Giesbert 6Max Giesbert 6
Please use the native formula function ISOWEEK() nowadays: https://help.salesforce.com/s/articleView?id=release-notes.rn_general_new_formula_functions.htm&release=238&type=5 (https://help.salesforce.com/s/articleView?id=release-notes.rn_general_new_formula_functions.htm&release=238&type=5)