You need to sign in to do that
Don't have an account?
Jennifer 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).
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).
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'); All the starting and ending dates are now correct for ... 2020.
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
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'); All the starting and ending dates are now correct for ... 2020.
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.
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
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.
In [ISO8601], the week number is defined by:
2021: week 1 starts on 4th of January while there is Friday 1st.
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).
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.
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:
2) Formula to find the Last Sunday in October of the current year:
https://success.salesforce.com/answers?id=90630000000Zb6oAAC
The next step is to have the check of the 4th of January for the first week.