+ Start a Discussion
Jennifer WilsonJennifer Wilson 

Formula for Week Number field not working - related to time

Hello
I'm trying to get the Week Number into a new custom field.  I need to calculate the week number from a standard Field Service Lightning field named "SchedStartTime".  The data type for this field is Date/Time.  I created a custom formula field to capture only the date from the SchedStartTime field named "Scheduled Start Date" with the formula: DATEVALUE(SchedStartTime ).  I then created another custom field named "Week Number" with the formula: MOD (FLOOR (( Scheduled_Start_Date__c - DATE(2019,12,30) + (1/24) ) / 7),53)+1.  However, there are errors when the Scheduled Start is midnight (00:00), the Week Number should update to the next week number but this isn't happening until the Scheduled Start is 01:00.  See example below:

Scheduled Start     Week Number
14/06/2020 23:00          24
14/06/2020 23:30          24
15/06/2020 00:00          24
15/06/2020 00:30          24
15/06/2020 01:00          25

Can anyone provide help please?  Note that our Org has the Default Time Zone(GMT+01:00) British Summer Time (Europe/London).
Best Answer chosen by Jennifer Wilson
Alain CabonAlain Cabon
Hello Jennifer,

The Week Number is not easy to find (the complete rules:   http://www.proesite.com/timex/wkcalc.htm ... but there is also the change of Daylight Saving Time )

The formula given by Salesforce in their samples is not good either (not correct far too simple).

There are two important dates in your case: 

1) 29 mar 2020 - Daylight Saving Time Started
2) 25 oct 2020 - Daylight Saving Time Ends

https://www.timeanddate.com/time/change/uk/london?year=2020

The formula below is a better approximation with hard coded dates for the DST starting and ending.

We can calculate them in the formula too ( the formula becomes huge but that could be possible ).

String strConvertedDate = t1.alcab__datetime1__c.format('dd/MM/yyyy HH:mm:ss', 'Europe/London');
IF( OR(DATEVALUE( alcab__datetime1__c ) < DATE( 2020, 3, 29 ) ,DATEVALUE( alcab__datetime1__c ) > DATE( 2020, 10, 24 )),
FLOOR ( ( ( DATEVALUE( alcab__datetime1__c  ) - DATE(  YEAR(DATEVALUE(alcab__datetime1__c))  ,1,1) 
+ MOD( DATEVALUE( alcab__datetime1__c   )
- DATE( 1900, 1, 7 ), 7 )+1))  / 7) +1,
FLOOR ( ( ( DATEVALUE( alcab__datetime1__c + 1/24 ) - DATE(  YEAR(DATEVALUE(alcab__datetime1__c))  ,1,1) 
+ MOD( DATEVALUE( alcab__datetime1__c   + 1/24 )
- DATE( 1900, 1, 7 ), 7 )+1))  / 7) +1)
All the starting and ending dates are now correct for ... 2020.
@istest
public class WeekNumberTEST {
    @istest
    private static void testMeth_start_week() {
        TestAll__c t = new TestAll__c();
        Date myDate = Date.newInstance(2020, 1, 1);
        Time myTime = Time.newInstance(0, 0, 0, 0);
        DateTime dt = DateTime.newInstance(myDate, myTime);
        t.alcab__datetime1__c = dt;
        insert t;
        
        Date myDate1 = Date.newInstance(2020, 1, 6);            
        List<TestAll__c> ta1 = new List<TestAll__c> ();
        for (integer i=1;i<53;i++) {
            TestAll__c t1 = new TestAll__c();
            Time myTime1 = Time.newInstance(0, 0, 0, 0);
            DateTime dt1 = DateTime.newInstance(myDate1, myTime1);
            t1.alcab__datetime1__c = dt1;
            ta1.add(t1);
            myDate1 += 7;
        }
        insert ta1;
           
        List<TestAll__c> ta = [select alcab__datetime1__c,alcab__Week_number2__c from TestAll__c order by  alcab__datetime1__c];
        integer expected = 1;
        for (TestAll__c t1:ta) {
            String strConvertedDate = t1.alcab__datetime1__c.format('dd/MM/yyyy HH:mm:ss',   'Europe/London');
            system.debug('test start week:' + strConvertedDate + ' - ' + t1.alcab__datetime1__c + ' = ' + t1.alcab__Week_number2__c);      
            system.assertEquals(expected,  t1.alcab__Week_number2__c);
            expected++;
        }
        
    }
    
    @istest
    private static void testMeth_end_week() {
        
        Date myDate1 = Date.newInstance(2020, 1, 5);            
        List<TestAll__c> ta1 = new List<TestAll__c> ();
        for (integer i=1;i<53;i++) {
            TestAll__c t1 = new TestAll__c();
            Time myTime1 = Time.newInstance(23, 59, 0, 0);
            DateTime dt1 = DateTime.newInstance(myDate1, myTime1);
            t1.alcab__datetime1__c = dt1;
            ta1.add(t1);
            myDate1 += 7;
        }
        insert ta1;
                
        List<TestAll__c> ta = [select alcab__datetime1__c,alcab__Week_number2__c from TestAll__c order by  alcab__datetime1__c];
        integer expected = 1;
        for (TestAll__c t1:ta) {
            String strConvertedDate = t1.alcab__datetime1__c.format('dd/MM/yyyy HH:mm:ss',   'Europe/London');
            system.debug('test end week:' +  strConvertedDate + ' - ' + t1.alcab__datetime1__c + ' = ' + t1.alcab__Week_number2__c);      
            system.assertEquals(expected,  t1.alcab__Week_number2__c);
            expected++;
        }      
    }   
}

But that works for 2020 only.  

2021:
1) 28 mar 2021 - Daylight Saving Time Starts
2) 31 oct 2021 - Daylight Saving Time Ends

That changes every year. It is a relative date according a number of weeks and precise day in the week that can also be calculated.
 

All Answers

Alain CabonAlain Cabon
Hello Jennifer,

The Week Number is not easy to find (the complete rules:   http://www.proesite.com/timex/wkcalc.htm ... but there is also the change of Daylight Saving Time )

The formula given by Salesforce in their samples is not good either (not correct far too simple).

There are two important dates in your case: 

1) 29 mar 2020 - Daylight Saving Time Started
2) 25 oct 2020 - Daylight Saving Time Ends

https://www.timeanddate.com/time/change/uk/london?year=2020

The formula below is a better approximation with hard coded dates for the DST starting and ending.

We can calculate them in the formula too ( the formula becomes huge but that could be possible ).

String strConvertedDate = t1.alcab__datetime1__c.format('dd/MM/yyyy HH:mm:ss', 'Europe/London');
IF( OR(DATEVALUE( alcab__datetime1__c ) < DATE( 2020, 3, 29 ) ,DATEVALUE( alcab__datetime1__c ) > DATE( 2020, 10, 24 )),
FLOOR ( ( ( DATEVALUE( alcab__datetime1__c  ) - DATE(  YEAR(DATEVALUE(alcab__datetime1__c))  ,1,1) 
+ MOD( DATEVALUE( alcab__datetime1__c   )
- DATE( 1900, 1, 7 ), 7 )+1))  / 7) +1,
FLOOR ( ( ( DATEVALUE( alcab__datetime1__c + 1/24 ) - DATE(  YEAR(DATEVALUE(alcab__datetime1__c))  ,1,1) 
+ MOD( DATEVALUE( alcab__datetime1__c   + 1/24 )
- DATE( 1900, 1, 7 ), 7 )+1))  / 7) +1)
All the starting and ending dates are now correct for ... 2020.
@istest
public class WeekNumberTEST {
    @istest
    private static void testMeth_start_week() {
        TestAll__c t = new TestAll__c();
        Date myDate = Date.newInstance(2020, 1, 1);
        Time myTime = Time.newInstance(0, 0, 0, 0);
        DateTime dt = DateTime.newInstance(myDate, myTime);
        t.alcab__datetime1__c = dt;
        insert t;
        
        Date myDate1 = Date.newInstance(2020, 1, 6);            
        List<TestAll__c> ta1 = new List<TestAll__c> ();
        for (integer i=1;i<53;i++) {
            TestAll__c t1 = new TestAll__c();
            Time myTime1 = Time.newInstance(0, 0, 0, 0);
            DateTime dt1 = DateTime.newInstance(myDate1, myTime1);
            t1.alcab__datetime1__c = dt1;
            ta1.add(t1);
            myDate1 += 7;
        }
        insert ta1;
           
        List<TestAll__c> ta = [select alcab__datetime1__c,alcab__Week_number2__c from TestAll__c order by  alcab__datetime1__c];
        integer expected = 1;
        for (TestAll__c t1:ta) {
            String strConvertedDate = t1.alcab__datetime1__c.format('dd/MM/yyyy HH:mm:ss',   'Europe/London');
            system.debug('test start week:' + strConvertedDate + ' - ' + t1.alcab__datetime1__c + ' = ' + t1.alcab__Week_number2__c);      
            system.assertEquals(expected,  t1.alcab__Week_number2__c);
            expected++;
        }
        
    }
    
    @istest
    private static void testMeth_end_week() {
        
        Date myDate1 = Date.newInstance(2020, 1, 5);            
        List<TestAll__c> ta1 = new List<TestAll__c> ();
        for (integer i=1;i<53;i++) {
            TestAll__c t1 = new TestAll__c();
            Time myTime1 = Time.newInstance(23, 59, 0, 0);
            DateTime dt1 = DateTime.newInstance(myDate1, myTime1);
            t1.alcab__datetime1__c = dt1;
            ta1.add(t1);
            myDate1 += 7;
        }
        insert ta1;
                
        List<TestAll__c> ta = [select alcab__datetime1__c,alcab__Week_number2__c from TestAll__c order by  alcab__datetime1__c];
        integer expected = 1;
        for (TestAll__c t1:ta) {
            String strConvertedDate = t1.alcab__datetime1__c.format('dd/MM/yyyy HH:mm:ss',   'Europe/London');
            system.debug('test end week:' +  strConvertedDate + ' - ' + t1.alcab__datetime1__c + ' = ' + t1.alcab__Week_number2__c);      
            system.assertEquals(expected,  t1.alcab__Week_number2__c);
            expected++;
        }      
    }   
}

But that works for 2020 only.  

2021:
1) 28 mar 2021 - Daylight Saving Time Starts
2) 31 oct 2021 - Daylight Saving Time Ends

That changes every year. It is a relative date according a number of weeks and precise day in the week that can also be calculated.
 
This was selected as the best answer
Alain CabonAlain Cabon
It is important to format the GMT date for your timezone.

String strConvertedDate = t1.alcab__datetime1__c.format('dd/MM/yyyy HH:mm:ss', 'Europe/London');

DEBUG|test end week: 05/01/2020 23:59:00 - GMT: 2020-01-05 23:59:00 = 1
DEBUG|test end week: 12/01/2020 23:59:00 - GMT: 2020-01-12 23:59:00 = 2
DEBUG|test end week: 19/01/2020 23:59:00 - GMT: 2020-01-19 23:59:00 = 3
DEBUG|test end week: 26/01/2020 23:59:00 - GMT: 2020-01-26 23:59:00 = 4
DEBUG|test end week: 02/02/2020 23:59:00 - GMT: 2020-02-02 23:59:00 = 5
DEBUG|test end week: 09/02/2020 23:59:00 - GMT: 2020-02-09 23:59:00 = 6
DEBUG|test end week: 16/02/2020 23:59:00 - GMT: 2020-02-16 23:59:00 = 7
DEBUG|test end week: 23/02/2020 23:59:00 - GMT: 2020-02-23 23:59:00 = 8
DEBUG|test end week: 01/03/2020 23:59:00 - GMT: 2020-03-01 23:59:00 = 9
DEBUG|test end week: 08/03/2020 23:59:00 - GMT: 2020-03-08 23:59:00 = 10
DEBUG|test end week: 15/03/2020 23:59:00 - GMT: 2020-03-15 23:59:00 = 11
DEBUG|test end week: 22/03/2020 23:59:00 - GMT: 2020-03-22 23:59:00 = 12
DEBUG|test end week: 29/03/2020 23:59:00 - GMT: 2020-03-29 22:59:00 = 13
DEBUG|test end week: 05/04/2020 23:59:00 - GMT: 2020-04-05 22:59:00 = 14
DEBUG|test end week: 12/04/2020 23:59:00 - GMT: 2020-04-12 22:59:00 = 15
DEBUG|test end week: 19/04/2020 23:59:00 - GMT: 2020-04-19 22:59:00 = 16
DEBUG|test end week: 26/04/2020 23:59:00 - GMT: 2020-04-26 22:59:00 = 17
DEBUG|test end week: 03/05/2020 23:59:00 - GMT: 2020-05-03 22:59:00 = 18
DEBUG|test end week: 10/05/2020 23:59:00 - GMT: 2020-05-10 22:59:00 = 19
DEBUG|test end week: 17/05/2020 23:59:00 - GMT: 2020-05-17 22:59:00 = 20
DEBUG|test end week: 24/05/2020 23:59:00 - GMT: 2020-05-24 22:59:00 = 21
DEBUG|test end week: 31/05/2020 23:59:00 - GMT: 2020-05-31 22:59:00 = 22
DEBUG|test end week: 07/06/2020 23:59:00 - GMT: 2020-06-07 22:59:00 = 23
DEBUG|test end week: 14/06/2020 23:59:00 - GMT: 2020-06-14 22:59:00 = 24
DEBUG|test end week: 21/06/2020 23:59:00 - GMT: 2020-06-21 22:59:00 = 25
DEBUG|test end week: 28/06/2020 23:59:00 - GMT: 2020-06-28 22:59:00 = 26
DEBUG|test end week: 05/07/2020 23:59:00 - GMT: 2020-07-05 22:59:00 = 27
DEBUG|test end week: 12/07/2020 23:59:00 - GMT: 2020-07-12 22:59:00 = 28
DEBUG|test end week: 19/07/2020 23:59:00 - GMT: 2020-07-19 22:59:00 = 29
DEBUG|test end week: 26/07/2020 23:59:00 - GMT: 2020-07-26 22:59:00 = 30
DEBUG|test end week: 02/08/2020 23:59:00 - GMT: 2020-08-02 22:59:00 = 31
DEBUG|test end week: 09/08/2020 23:59:00 - GMT: 2020-08-09 22:59:00 = 32
DEBUG|test end week: 16/08/2020 23:59:00 - GMT: 2020-08-16 22:59:00 = 33
DEBUG|test end week: 23/08/2020 23:59:00 - GMT: 2020-08-23 22:59:00 = 34
DEBUG|test end week: 30/08/2020 23:59:00 - GMT: 2020-08-30 22:59:00 = 35
DEBUG|test end week: 06/09/2020 23:59:00 - GMT: 2020-09-06 22:59:00 = 36
DEBUG|test end week: 13/09/2020 23:59:00 - GMT: 2020-09-13 22:59:00 = 37
DEBUG|test end week: 20/09/2020 23:59:00 - GMT: 2020-09-20 22:59:00 = 38
DEBUG|test end week: 27/09/2020 23:59:00 - GMT: 2020-09-27 22:59:00 = 39
DEBUG|test end week: 04/10/2020 23:59:00 - GMT: 2020-10-04 22:59:00 = 40
DEBUG|test end week: 11/10/2020 23:59:00 - GMT: 2020-10-11 22:59:00 = 41
DEBUG|test end week: 18/10/2020 23:59:00 - GMT: 2020-10-18 22:59:00 = 42
DEBUG|test end week: 25/10/2020 23:59:00 - GMT: 2020-10-25 23:59:00 = 43
DEBUG|test end week: 01/11/2020 23:59:00 - GMT: 2020-11-01 23:59:00 = 44
DEBUG|test end week: 08/11/2020 23:59:00 - GMT: 2020-11-08 23:59:00 = 45
DEBUG|test end week: 15/11/2020 23:59:00 - GMT: 2020-11-15 23:59:00 = 46
DEBUG|test end week: 22/11/2020 23:59:00 - GMT: 2020-11-22 23:59:00 = 47
DEBUG|test end week: 29/11/2020 23:59:00 - GMT: 2020-11-29 23:59:00 = 48
DEBUG|test end week: 06/12/2020 23:59:00 - GMT: 2020-12-06 23:59:00 = 49
DEBUG|test end week: 13/12/2020 23:59:00 - GMT: 2020-12-13 23:59:00 = 50
DEBUG|test end week: 20/12/2020 23:59:00 - GMT: 2020-12-20 23:59:00 = 51
DEBUG|test end week: 27/12/2020 23:59:00 - GMT: 2020-12-27 23:59:00 = 52


DEBUG|test start week: 01/01/2020 00:00:00 - GMT: 2020-01-01 00:00:00 = 1
DEBUG|test start week: 06/01/2020 00:00:00 - GMT: 2020-01-06 00:00:00 = 2
DEBUG|test start week: 13/01/2020 00:00:00 - GMT: 2020-01-13 00:00:00 = 3
DEBUG|test start week: 20/01/2020 00:00:00 - GMT: 2020-01-20 00:00:00 = 4
DEBUG|test start week: 27/01/2020 00:00:00 - GMT: 2020-01-27 00:00:00 = 5
DEBUG|test start week: 03/02/2020 00:00:00 - GMT: 2020-02-03 00:00:00 = 6
DEBUG|test start week: 10/02/2020 00:00:00 - GMT: 2020-02-10 00:00:00 = 7
DEBUG|test start week: 17/02/2020 00:00:00 - GMT: 2020-02-17 00:00:00 = 8
DEBUG|test start week: 24/02/2020 00:00:00 - GMT: 2020-02-24 00:00:00 = 9
DEBUG|test start week: 02/03/2020 00:00:00 - GMT: 2020-03-02 00:00:00 = 10
DEBUG|test start week: 09/03/2020 00:00:00 - GMT: 2020-03-09 00:00:00 = 11
DEBUG|test start week: 16/03/2020 00:00:00 - GMT: 2020-03-16 00:00:00 = 12
DEBUG|test start week: 23/03/2020 00:00:00 - GMT: 2020-03-23 00:00:00 = 13
DEBUG|test start week: 30/03/2020 00:00:00 - GMT: 2020-03-29 23:00:00 = 14
DEBUG|test start week: 06/04/2020 00:00:00 - GMT: 2020-04-05 23:00:00 = 15
DEBUG|test start week: 13/04/2020 00:00:00 - GMT: 2020-04-12 23:00:00 = 16
DEBUG|test start week: 20/04/2020 00:00:00 - GMT: 2020-04-19 23:00:00 = 17
DEBUG|test start week: 27/04/2020 00:00:00 - GMT: 2020-04-26 23:00:00 = 18
DEBUG|test start week: 04/05/2020 00:00:00 - GMT: 2020-05-03 23:00:00 = 19
DEBUG|test start week: 11/05/2020 00:00:00 - GMT: 2020-05-10 23:00:00 = 20
DEBUG|test start week: 18/05/2020 00:00:00 - GMT: 2020-05-17 23:00:00 = 21
DEBUG|test start week: 25/05/2020 00:00:00 - GMT: 2020-05-24 23:00:00 = 22
DEBUG|test start week: 01/06/2020 00:00:00 - GMT: 2020-05-31 23:00:00 = 23
DEBUG|test start week: 08/06/2020 00:00:00 - GMT: 2020-06-07 23:00:00 = 24
DEBUG|test start week: 15/06/2020 00:00:00 - GMT: 2020-06-14 23:00:00 = 25
DEBUG|test start week: 22/06/2020 00:00:00 - GMT: 2020-06-21 23:00:00 = 26
DEBUG|test start week: 29/06/2020 00:00:00 - GMT: 2020-06-28 23:00:00 = 27
DEBUG|test start week: 06/07/2020 00:00:00 - GMT: 2020-07-05 23:00:00 = 28
DEBUG|test start week: 13/07/2020 00:00:00 - GMT: 2020-07-12 23:00:00 = 29
DEBUG|test start week: 20/07/2020 00:00:00 - GMT: 2020-07-19 23:00:00 = 30
DEBUG|test start week: 27/07/2020 00:00:00 - GMT: 2020-07-26 23:00:00 = 31
DEBUG|test start week: 03/08/2020 00:00:00 - GMT: 2020-08-02 23:00:00 = 32
DEBUG|test start week: 10/08/2020 00:00:00 - GMT: 2020-08-09 23:00:00 = 33
DEBUG|test start week: 17/08/2020 00:00:00 - GMT: 2020-08-16 23:00:00 = 34
DEBUG|test start week: 24/08/2020 00:00:00 - GMT: 2020-08-23 23:00:00 = 35
DEBUG|test start week: 31/08/2020 00:00:00 - GMT: 2020-08-30 23:00:00 = 36
DEBUG|test start week: 07/09/2020 00:00:00 - GMT: 2020-09-06 23:00:00 = 37
DEBUG|test start week: 14/09/2020 00:00:00 - GMT: 2020-09-13 23:00:00 = 38
DEBUG|test start week: 21/09/2020 00:00:00 - GMT: 2020-09-20 23:00:00 = 39
DEBUG|test start week: 28/09/2020 00:00:00 - GMT: 2020-09-27 23:00:00 = 40
DEBUG|test start week: 05/10/2020 00:00:00 - GMT: 2020-10-04 23:00:00 = 41
DEBUG|test start week: 12/10/2020 00:00:00 - GMT: 2020-10-11 23:00:00 = 42
DEBUG|test start week: 19/10/2020 00:00:00 - GMT: 2020-10-18 23:00:00 = 43
DEBUG|test start week: 26/10/2020 00:00:00 - GMT: 2020-10-26 00:00:00 = 44
DEBUG|test start week: 02/11/2020 00:00:00 - GMT: 2020-11-02 00:00:00 = 45
DEBUG|test start week: 09/11/2020 00:00:00 - GMT: 2020-11-09 00:00:00 = 46
DEBUG|test start week: 16/11/2020 00:00:00 - GMT: 2020-11-16 00:00:00 = 47
DEBUG|test start week: 23/11/2020 00:00:00 - GMT: 2020-11-23 00:00:00 = 48
DEBUG|test start week: 30/11/2020 00:00:00 - GMT: 2020-11-30 00:00:00 = 49
DEBUG|test start week: 07/12/2020 00:00:00 - GMT: 2020-12-07 00:00:00 = 50
DEBUG|test start week: 14/12/2020 00:00:00 - GMT: 2020-12-14 00:00:00 = 51
DEBUG|test start week: 21/12/2020 00:00:00 - GMT: 2020-12-21 00:00:00 = 52
DEBUG|test start week: 28/12/2020 00:00:00 - GMT: 2020-12-28 00:00:00 = 53

Best regards
 
Jennifer WilsonJennifer Wilson
Thank you Alain!
Alain CabonAlain Cabon
Hello Jennifer, 

This is a first formula for 2020 only (verified with a class test) but it is too restricted.

I will post here a formula that will work for all the years with a good approximation but there is a problem of compilation limit size that blocks the final save when there are many calls to DATEVALUE.

I have almost solved the final step. It is a very complicated problem because of the limitations of Salesforce for the formulas.  
Alain CabonAlain Cabon
The rules for the numbering of the weeks have a specific rule for the first week and that complicates the formulas..

In [ISO8601], the week number is defined by:
  • weeks start on a monday
  • week 1 of a given year is the one that includes the first Thursday of that year. (or, equivalently, week 1 is the week that includes 4 January.) or a week with a minimal length of two days before the week-end.

2021: week 1 starts on 4th of January while there is Friday 1st.

User-added image

2026: Thursday exists, the first week has the minimal two days before the week-end (requirement) and starts on the 1st of January (as expected).

User-added image

With the limitations of size for the formulas and the complicated rules for the week number, most of the "short" formulas are approximations with errors for the first week at least (and that is a problem).

With Apex, it is easy but with formulas, it is a puzzle.
 
Alain CabonAlain Cabon
The problem of Daylight Savings Time Formula could have a solution for a specific time zone.
https://developer.salesforce.com/forums/?id=9060G0000005rSEQAY

United Kingdom: Europe Northern = Last Sunday March at 01:00 UTC  <=> Last Sunday October at 01:00 UTC
https://en.wikipedia.org/wiki/Daylight_saving_time_by_country

1) Formula to find the Last Sunday in March of the current year:
DATE(YEAR(DATEVALUE( alcab__datetime1__c ) ),3,31)
-
MOD(DATE(YEAR(DATEVALUE( alcab__datetime1__c ) ),3,31) - DATE(1900,1,7),7)

2) Formula to find the Last Sunday in October of the current year:
DATE(YEAR(DATEVALUE(alcab__datetime1__c)),10,31)
-
MOD(DATE(YEAR(DATEVALUE(alcab__datetime1__c)),10,31) - DATE(1900,1,7),7)

https://success.salesforce.com/answers?id=90630000000Zb6oAAC

The next step is to have the check of the 4th of January for the first week.