Zoho Deluge: Get the Week Number if Week starts on a Monday

: Joel Lipman; Zoho; 06 July 2023; Hits: 1613

What?
A quick article on determining the week number of the year if your week starts on a Monday and not a Sunday.

Why?
I think it's the US system which generally says that a week starts on a Sunday but in the United Kingdom, the week generally starts on a Monday. So let's say there's a schedule that has to be run weekly and include all the orders of a specific week...

Just to confuse everyone in the world, the day index for a Sunday is 7 (ie. myDate.toString("u") returns 7) and the week commences on a Sunday (ie. myDate.toStartOfWeek("EEEE") returns Sunday). In other words, the day counting starts at 7, followed by 1 for Monday, 2 for Tuesday, and so on...

How?
The logic behind this would be to take the date of the order, check if the order date is a Sunday, subtract a day, and return the week number:

copy raw

c_Order = Order[ID == 123456890123456890];
if(c_Order.Order_Date != null)
{
    v_ThisDate = c_Order.Order_Date.toString("EEEE, dd-MMM-yyyy u w");
	v_ThisWeek = if(c_Order.Order_Date.toString("u").toLong()==7,c_Order.Order_Date.subDay(1),c_Order.Order_Date).toString("w");
}
// Monday, 26-Jun-2023 1 26
// yields: 26 
//
// Sunday, 02-Jul-2023 7 27
// yields: 26 
//
// Monday, 03-Jul-2023 1 27
// yields: 27 
//
// Sunday, 09-Jul-2023 7 28
// yields: 27

c_Order = Order[ID == 123456890123456890];
if(c_Order.Order_Date != null)
{
v_ThisDate = c_Order.Order_Date.toString("EEEE, dd-MMM-yyyy u w");
v_ThisWeek = if(c_Order.Order_Date.toString("u").toLong()==7,c_Order.Order_Date.subDay(1),c_Order.Order_Date).toString("w");
}
// Monday, 26-Jun-2023 1 26
// yields: 26
//
// Sunday, 02-Jul-2023 7 27
// yields: 26
//
// Monday, 03-Jul-2023 1 27
// yields: 27
//
// Sunday, 09-Jul-2023 7 28
// yields: 27

Originally, I had this formula which seemed to do the same thing but unnecessarily moved the pointer to the start of the week. It could also error eventually if Zoho made it possible to change the start of the week (this is in ZohoCreator; I'm aware that some of Zoho Apps do allow this) in the app which would possibly affect the functionality of .toStartOfWeek().

copy raw

v_ThisWeek = if(c_Order.Order_Date.toStartOfWeek() == c_Order.Order_Date,c_Order.Order_Date.toStartOfWeek().subDay(1),c_Order.Order_Date.toStartOfWeek()).toString("w");

v_ThisWeek = if(c_Order.Order_Date.toStartOfWeek() == c_Order.Order_Date,c_Order.Order_Date.toStartOfWeek().subDay(1),c_Order.Order_Date.toStartOfWeek()).toString("w");

Additional:

I'm just adding a little extra based on the further feedback, if we're generating a reporting year and week, we have to look at the difference between .toString("y") and .toString("Y"). Lowercase 'y' is the year and uppercase 'Y' is the year the week is in. Weeks which overlap at the end/start of the year need to be observed. As per the documentation:
copyrawstyler
```
dateValue = '29-Dec-2019';
info dateValue.toString("yyyy"); // returns 2019

dateValue = '29-Dec-2019';
info dateValue.toString("YYYY"); // returns 2020
```
1. dateValue = '29-Dec-2019';
2. info dateValue.toString("yyyy"); // returns 2019
4. dateValue = '29-Dec-2019';
5. info dateValue.toString("YYYY"); // returns 2020
You should also pad single digit week numbers if we're going to store a year week value:
copyrawstyler
```
v_ThisCurrentWeekNumber = if(zoho.currentdate.toString("u").toLong() == 7,zoho.currentdate.subDay(1),zoho.currentdate).toString("w");
v_ThisCurrentWeekNumberPadded = v_ThisCurrentWeekNumber.leftPad(2).replaceAll(" ", "0", true);
//
// or
//
v_ThisCurrentWeekNumber = if(zoho.currentdate.toString("u").toLong() == 7,zoho.currentdate.subDay(1),zoho.currentdate).toString("ww");
//
// 7 returns '07'
// 52 return '52'
```
1. v_ThisCurrentWeekNumber = if(zoho.currentdate.toString("u").toLong() == 7,zoho.currentdate.subDay(1),zoho.currentdate).toString("w");
2. v_ThisCurrentWeekNumberPadded = v_ThisCurrentWeekNumber.leftPad(2).replaceAll(" ", "0", true);
3. //
4. // or
5. //
6. v_ThisCurrentWeekNumber = if(zoho.currentdate.toString("u").toLong() == 7,zoho.currentdate.subDay(1),zoho.currentdate).toString("ww");
7. //
8. // 7 returns '07'
9. // 52 return '52'