Salesforce ISOWEEK Function

Do you want to learn the ISOWEEK function in Salesforce? In this Salesforce tutorial, you will learn about the ISOWEEK function with its syntax, examples, and use cases.

Have you ever faced any issues with different conventions of the week numbering system? You want to represent the week in a standardized way, across different cultures and regions.

If yes, Salesforce provides us with the “ISOWEEK” function that can be used to manipulate and extract information from date and time fields and represent it in a consistent format throughout the world.

Let’s move ahead and learn step-by-step how the ISOWEEK function works in Salesforce Lightning and Salesforce Classic in the formula field with the help of real business-based examples.

What is ISOWEEK Function in Salesforce

The ISOWEEK function in Salesforce is one of the date and time category functions that can used to work up with the week of the date and date/time fields.

The Salesforce ISOWEEK function is used to return the ISO week number from a given date. The ISO 8601 week number system is defined by the ISO 8601 standard to give an accurate and internationally recognized way of representing weeks.

Additionally, the ISOWEEK function returns the week from 1-52 or 53, depending on the year, so that the first week starts on Monday. Moreover, you can use this function in use cases such as Project Management, Financial Reporting, Resource Planning, Payment Systems, etc.

With this, you have learned about the ISOWEEK function in Salesforce. In the next section, you will explore the syntax of the ISOWEEK function in Salesforce.

Syntax of ISOWEEK Function in Salesforce

The syntax for the ISOWEEK function is as given below:

ISOWEEK(date)

Here:

  • date: The date parameters take the date field or the date/time field for which you want to find the ISO 8601 week number.

Return: The ISOWEEK function will take the return of the week in the numeric value depending upon the year. It is important to note that you select the number data type in the formula return type.

With this, you have learned how to write the ISOWEEK function in Salesforce. Moving ahead, you will learn the basic examples of the ISOWEEK function so that you will understand the working of the function.

Examples of ISOWEEK Function in Salesforce

In this example, below is the given table that explains the working and logic behind the ISOWEEK function in Salesforce.

Here, in the table, we have the “Input” column that shows the input date you passed as the parameter in the function and the “Output” column represents the output of the result returned by the ISOWEEK function in Salesforce.

Input ExpressionOutput Result
ISOWEEK(Order_Date__c). Suppose Order_Date__c is “05/20/2024” that is on Monday.21 (The week containing May 20, 2024, starts on a Monday, so it is part of ISO week 21.)
ISOWEEK(Due_Date__c). Suppose Due_Date__c is “12/314/2024” that is on Tuesday.1 (The last few days of 2024, including December 31st, are part of ISO week 1 of 2025.)
ISOWEEK(Registration_Date__c). Suppose Registration_Date__c is “01/02/2025” that is on Thursday.1 (The week containing January 2nd is ISO week 1 because it includes the first Thursday of the year.)
ISOYEAR(Project_Proposal_Date__c). Suppose Project_Proposal_Date__c is “12/31/2027” that is on Saturday.52 (The last few days of 2027, including December 31st, are part of ISO week 52 of 2027.)
ISOYEAR(Expiry_Date__c). Suppose Expiry_Date__c is “12/31/2029” that is on Monday.1 (The majority of the week falls in ISO year by the calendar. ISO week 1 of 2030 includes the majority of December 31, 2029.)

With this, you have learned how the ISOWEEK functions workers with the rules of the ISO 8601 system. Next, you will explore how to use the ISOWEEK function in Salesforce Lightning.

See also  ISPICKVAL() Function in Salesforce

How to use the ISOWEEK Function in Salesforce Lightning

Let’s discuss how to use the ISOWEEK function with the help of the scenario. Suppose you want to give a discount on the order if the payment is done within ISO week of the bill generated date and the total amount is above 1000.

The steps required to use Salesforce Lightning’s ISOWEEK function are as follows:

1. In the Lightning Interface log in as an admin to the Salesforce account. Click the “Gear” symbol on the right side, then choose the “Setup” option from the dropdown menu if you are not already on the main “Home Setup” page.

2. Select the “Object Manager” tab from the navigation bar.

Salesforce ISOWEEK Function

3. Use the “Quick Find” box to search for the name of the object you want to apply the ISOWEEK function to. According to the scenario, you must look for the object named “Orders”.

Salesforce ISOWEEK Function Example

4. Select the “Fields & Relationships” option from the left sidebar. Then, to create a new field, click the “New” button on the right.

ISOWEEK Function in Salesforce

5. Select the data type to indicate the information you want to save. Click the radio option next to the “Formula” data type to begin using the function. Next, click the “Next” button to continue.

ISOWEEK Function in Salesforce Example

6. After entering the “Field Label”, click the “Field Name” (which will be automatically filled in depending on the field label, but you can edit it if you would like).

You can enter the field label “Discount” according to the scenario.

Choose the data type from the list as per the formula’s requirements by scrolling down to the “Formula Return Type” section. The scenario indicates that you must select the “Currency” data type.

If you want to reset the “Decimal Places”, you can do that also, by default its value is 2. After that, click on the “Next” button to move to the next step.

Salesforce Lightning ISOWEEK Function

7. To build the advanced formula, select the “Advanced Formula” option. Click the “All Functions Categories” dropdown menu from the “Functions” section and choose the necessary function category.

For instance, choose the “Date and Time” category since you require the “ISOWEEK” function. Next, look through the category for the necessary function and click on it.

Click the “Insert Selected Function” button found under the function section after you’ve chosen the function. Then, in accordance with the scenario, substitute the merger fields, expressions, or functions for the function’s parameters.

For example, as per the scenario you want to calculate the discount on the order based on the ISO week. The formula for the scenario is given below:

IF(AND(ISOWeek(TODAY()) - ISOWeek( Bill_Generated_Date__c ) <= 1,  Total_Amount__c  > 1000), Total_Amount__c* 0.98, Total_Amount__c)

Here is the detailed description of the formula:

  • ISOWEEK(TODAY()): This function calculates the ISO week number for the present date.
  • ISOWEEK(Bill_Generated_Date__c): This function is used to calculate the ISO week number of the bill-generated date field.
  • ISOWEEK(TODAY()) – ISOWeek(Bill_Generated_Date__c): It is used to find out the difference in ISO week numbers between the current date and the bill-generated date.
  • Total_Amount__c >1000: It checks whether the total amount value is greater than 1000 or not.
  • AND(ISOWeek(TODAY()) – ISOWeek(Bill_Generated_Date__c) <= 1, Total_Amount__c > 1000): It collectively checks if both the conditions are true i.e. difference in ISO week numbers is less than or equal to 1, and the total amount is more than 1000.
  • IF() function: The function is used to give the result on the basis of the conditions such as:
    • If AND conditions are True: Total_Amount__c * 0.98 calculates 98% of the total amount. In other words, it gives a 2% discount on the order.
    • If any of the AND conditions are False: It returns the original total amount without any discount on the order.
See also  IMAGE() Function in Salesforce

Click the “Check Syntax” button to see whether there are any mistakes in the formula or whether it is correct. If there is no error, you will receive the message “No Syntax error in merge fields or function.”. You will receive an error message otherwise.

You can optionally add further information by entering the “Description” and “Help Text”. Then, to continue, click the “Next” button.

Salesforce Lightning ISOWEEK Function Example

8. Choose the profiles to whom you wish to provide field edit access in order to provide field-level security. Some of the profiles are chosen by default.

Click the checkbox next to each profile name in the “Visible” column to allow access to further profiles. Additionally, by selecting the checkbox next to the profile in the “Read Only” column, you can provide read-only access.

Click on the “Next” button to proceed.

ISOWEEK Function in Salesforce Lightning

9. Add the field to the page layout in this step. Every page layout is chosen by default. Uncheck that page layout, though, if you don’t want to add the custom field on any of them.

The field will be added at the end of the first two-column area of the page layout. Next, select the “Save” button to finish utilizing Salesforce Lightning’s ISOWEEK() function.

ISOWEEK Function in Salesforce Lightning Example

10. Once the formula field is created, you will be able to use this formula field. Let’s see how it works:

  • Open the “Order” item, create a new order with the fields “Bill Generated Date” and “Total Amount” and save the order.
  • Then, move to the “Details Section” where you will see the “Discount Price” field with the output of the ISOWEEK function.

Let’s understand it with the example:

If you have created an order with the bill-generated date “01/05/2023”, total amount “800”, and today date “01/09/2024”. After the execution of the formula, you will get the output for the discount price “800” because the ISO week difference is greater than 1, and the total amount is not greater than $1000. Therefore, the formula results in no discount.

If you have created an order with the bill-generated date “01/12/2024”, total amount “1200”, and today date “01/09/2024”. After the execution of the formula, you will get the output for the discount price “1176” because the ISO week difference between these two dates is 0, and the total amount is greater than 1200. Therefore, the formula returns the result with a 20% discount.

How to use the ISOWEEK Function in Salesforce Lightning

By the end, you have learned how to use the ISOWEEK function in Salesforce Lightning. Let’s proceed ahead and discuss how to work with the ISOWEEK function in Salesforce Classic Interface.

How to use ISOWEEK Function in Salesforce Classic

Suppose a scenario in which you want to get the subscription renewal reminder using the ISOWeek function depending upon the ISO week to understand how to use the ISOWEEK function in Classic.

Let’s look at the procedures for creating a formula field in Salesforce Classic and use the ISOWEEK function:

1. As an administrator, log onto the Salesforce Classic account. Next, select the “Setup” option that is situated in the upper right corner of the page.

Note: Click on the dropdown next to your name to bring up the “Setup” option if you are unable to see it here.

2. Next, utilize the left-hand “Quick Find” box to search for the object name in the area where you wish to apply the ISOWEEK function. In accordance with the scenario described above, you must look for “Contracts”.

Select the “Fields” option from the dropdown to start the process of creating a new field.

Salesforce Classic ISOWEEK Function

In the upcoming step, scroll down to the “Custom Fields & Relationships” section and click on the “New” button.

3. In this step, select the field data type by clicking on the radio button next to it to determine the kind of data that will be entered into the custom field.

See also  Salesforce DATEVALUE Function

You must choose the “Formula” data type in accordance with the case since we wish to use the ISOWEEK function to develop an advanced formula.

ISOWEEK Function in Salesforce Classic

4. After entering the “Filed Label” for the formula field and clicking “Field Name”, it automatically populates and is filled in, but you can modify it if you do not like it. As per the example, I name the field label “Renewal Reminder Status”.

5. Select the relevant data type for the return type in the “Formula Return Type” section by selecting the radio button next to the chosen data type. In accordance with the example, I choose the “Text” as the return data type.

ISOWEEK Function in Salesforce Classic Example

6. Click on the advanced formula sub-tab, select the necessary function category from the “All Functions Categories” section, and then choose the required functions and insert it with the use of the “Insert Selected Function”.

To complete the formula, substitute the fields, operators, and functions for the parameters once the function has been typed into the advanced formula box.

In accordance with the scenario, you must select the “ISOWEEK” function by clicking on the “Date and Time” category, and then change the parameters according to the formula that shows the message for the subscription renewal reminders.

Below is the formula for that:

IF(ISOWeek(Renewal_Date__c) = ISOWeek(TODAY()), "Renewal Due This Week", "Renewal Due Later")
  • ISOWeek(Renewal_Date__c): This function calculates the ISO week number for the stored renewal date.
  • ISOWeek(TODAY()): The function calculates the ISO week number for the current date.
  • ISOWeek(Renewal_Date__c) = ISOWeek(TODAY()): It checks if the ISO week numbers of the renewal date and the today date are equal.
  • IF() Function: It gives the result as per the conditions:
    • If the renewal is due in the same ISO week as the current date, it returns “Renewal Due This Week”.
    • If the renewal is not due in the current ISO week, it returns “Renewal Due Later”.

7. Select the “Check Syntax” option to see if there are any errors in the formula. It will show the error message if there is a problem. “No Synatx error in merge fields or function” appears otherwise.

You can optionally add “Description” and “Help Text” to provide more details about the formula field. Click the “Next” button to move on.

How to use ISOWEEK Function in Salesforce Classic

8. You will choose in which profiles to want to provide field-level security to provide edit access to this field. The field will be hidden from the specific profile if you haven’t chosen it.

To further customize the options, you can check and uncheck the checkbox next to the profile. Then, click on the “Next” button and proceed.

How to use ISOWEEK Function in Salesforce Classic Example

9. Select the page layout in which you wish to include this field now. If you don’t select a page layout, it won’t show up on any pages.

All of the page layouts are chosen by default, but you can deselect the checkbox and change that if you don’t want the field to be part of that specific page layout.

After that, click on the “Save” button to complete the process of creating a formula field with the ISOWEEK function used in Salesforce.

Use of ISOWEEK Function in Salesforce Classic

10. This formula field is now usable. Let’s go over the procedure:

  • Open the “Contracts” tab, create a new contract with the “Renewal Date”, and save it.
  • After that, move to the details section page of the contract object and locate the “Renewal Reminder Status” field that will produce the result based on the ISOWEEK function.

Let’s understand it with the example:

Suppose you have created a contract with the renewal date “01/22/2024” and today’s date “01/15/2024”. After the execution of the formula, you will get the output for the renewal subscription reminder as “Renewal Due Later”.

Example of ISOWEEK Function in Salesforce

With this, you have learned how to use the ISOWEEK function in the formula field in Salesforce Classic.

Conclusion

In this article, you have learned how to use the Salesforce ISOWEEK function, which returns the ISO 8601-digit week where the first day is considered as Monday.

Furthermore, we discussed its use case, which is to solve the issue of varied weekdays in different cultures. Additionally, we have learned the syntax and see detailed implementation for using Salesforce Lightning and Salesforce Classic ISOWEEK function.

You may like to read: