Salesforce FROMUNIXTIME Function

The Salesforce FROMUNIXTIME function is used to manage and manipulate date and time data effectively as it is used to handle Unix timestamps within the Salesforce environment.

Many Salesforce admin learners frequently inquire about the FROMUNIXTIME function in Salesforce.

If you look online, you do not get any results regarding the FORMUNIXTIME function. As a Salesforce Administrator, I will help you understand the complete workings of the function from beginning to end.

In this Salesforce tutorial, I am going to explain the FORMUNIXTIME function with its syntax, example, and use case. You will also be guided on how to use this function in both Salesforce Lightning and Salesforce Classic Interface.

Salesforce FORMUNIXTIME Function

What do we mean by Unix Timestamps?

As I said, the function handles Unix timestamps within the Salesforce environment, so it is very important to understand the concept of Unix timestamps.

The Unix timestamp is a value that indicates how many seconds have passed since January 1, 1970, at 00:00:00 UTC. It is also known as Epoch Time.

These timestamps are commonly used for recording and managing time-related information in various systems.

What is the FORMUNIXTIME Function in Salesforce, and How is it linked with Unix Timestamps?

It is one of the Date/Time functions of Salesforce that is used to do advanced manipulation with the date and time of the Salesforce system.

The Salesforce FORMUNIXTIME Function gives the datetime reprsenting the number of seconds elapsed since 1 Jan 1970. It is linked with Unix Timestamps as it is used to convert Unix timestamps into Salesforce DateTime values.

How should we write the FORMUNIXTIME function in Salesforce?

The syntax of the FORMUNIXTIME function is as given below:

FROMUNIXTIME(number)

Here:

number: The number parameter represents the Unix timestamp in seconds that needs to be converted into a Salesforce DateTime value.

Additionally, the function returns the DateTime that represents the given number as the seconds elapsed since 1 Jan 1970. It is important to note that it returns the result in the Date and Time format.

So, make sure that you have chosen the “Date/Time” as the formula return data type.

Where do we use the FORMUNIXTIME function in Salesforce?

We can use this FORMUNIXTIME function when we have imported the data from external systems that utilize Unix timestamps, and we want to map it with Salesforce DateTime fields.

We can use it when we want to convert Unix timestamps into a human-readable DateTime format for maintaining consistency and accuracy in data representation.

FORMUNIXTIME function examples in Salesforce

The below given-table explains the examples related to the FORMUNIXTIME function in Salesforce so we can easily understand how it works.

Input ParamterOutput Result
FORMUNIXTIME(1767241600)05/31/2024 12:00:00
FORMUNIXTIME(1832620800)01/01/2025 00:00:00
FORMUNIXTIME(1704067200)09/30/2024 12:00:00

In the table, the “Input” column indicates the unix timestamp in the second in the number provided as the parameter in the function by the user. On the other hand, the “Output” column illustrates the outcome generated by the FORMUNIXTIME function in Salesforce.

How we use the FORMUNIXTIME function in Salesforce Lightning

The following are the steps to use the FORMUNIXTIME function in Salesforce Lightning.

Let’s discuss it with an example: Suppose you import the lead engagement data with Unix timestamps and want to convert it into human-readable date and time.

1. Login to the Salesforce account in the Lightning Experience. Click on the “Gear” symbol on the right side of the page after logging in if you are not on the main home page setup. From the dropdown menu, select the “Setup” (Setup for current app) option.

2. After you’ve successfully navigated to the Lightning Experience’s main home page setup, click “Object Manager” from the navigation bar.

See also  Salesforce SECOND Function

Now utilise the “Quick Find” box to look for the object that you wish to use the Salesforce function for. In accordance with the scenario, look for and click on the “Lead” object.

3. From the left side, click on the “Field & Relationship” option, and after that, click on the “New” button as you want to use the FORMUNIXTIME function in the formula field. So, for this, you need to create the field first.

Salesforce FROMUNIXTIME Function Example

4. In this step, you will get all the Salesforce data types and choose one from it as per the requirement. According to our requirement, you need to choose formula one as we are going to create a formula field with the function.

For this, we click on the radio button next to the “Formula” field type and then click on the “Next” button to move to the next step.

Salesforce Lightning FROMUNIXTIME Function

5. Now, it’s time to set up the field label and formula return type. So, firstly, I entered the “Filed Label”, and when I clicked on the “Field Name”, it automatically filled up.

In accordance with the scenario, you can enter the field label as “Lead Engagement”.

We were given the option to select the formula return type from the list of available field types once we scrolled down. However, as we know, the Salesforce FORMUNIXTIME function only provides a date and time form of the result.

As per the scenario, choose “Date/Time” by clicking the radio button that is next to it. After that, click on the “Next” button and proceed to the next step.

6. Click on the “Advance Formula” subtab to enter the formula with the use of additional fields, operators, and functions.

After that, move to the “Function Section” select “All Function Categories” -> click on “Date and Time” -> search, and select “FORMUNIXTIME” -> click “Insert Selected Function”.

If you require more functions, you can add them similarly, and to add the fields and operators, click on the “Insert Field” button and “Insert Operator” select, respectively.

For example, according to the scenario, we want to get the human-readable date and time of the lead engagement data that you store in an external tool in Unix timestamps.

Below is the formula for that:

IF(NOT(ISNULL( Lead_External_Timestamp__c )), FROMUNIXTIME(Lead_External_Timestamp__c), NULL)

Here is a detailed explanation of the formula:

  • IF() Function: It is a conditional statement; it checks if the condition is true, and it returns the true value; otherwise, it returns the false value.
  • ISNULL(Lead_External_Timestamp__c): Checks, if the field leads external timestamp, is null.
  • NOT(): Negates the result if the lead external timestamp is not null.
  • FROMUNIXTIME(Lead_External_Timestamp__c): This function is used to convert a Unix timestamp (seconds since January 1, 1970) to a Salesforce date/time.
  • NULL: If the lead external timestamp is null, it returns the result of the formulas as null.

Now, click on the “Check Syntax” button to verify that the formula has no error. If there is no error, we will see the message “No syntax errors in merge fields or function”. But if there is an error, we will see an error message on the screen.

We can also define the “Description” and “Help Text”, if we want. After that, click on the “Next” button to move to the next step.

FORMUNIXTIME Function in Salesforce

7. Now, it’s time to setup the “Field Level Security”. Here, we have to choose the profile to whom we want to grant the field-level security edit access to this field.

We can also give access to read-only if we want by checking the “Read Only” checkboxes. After that, I click on the “Next” button to move to the next step.

FORMUNIXTIME Function in Salesforce Example

8. In this step, select the page layout that should include this field. The field will be added as the last field in the first 2-column section of these page layouts.

See also  Salesforce YEAR Function

The field will not appear on any page if we do not select a layout. We can also change the location of this field on the page, but for this, we need to customize the page layout.

After that, click on the “Save” button to complete the process of creating the field.

FORMUNIXTIME Function in Salesforce Lightning

9. Once the formula field is created, you can use the formula field. Let’s see an example:

  • Open the “Leads” item, create a new lead with the “Lead External Timestamp”, and save the lead.
  • Now move to the “Leads Details” section, and here, we will get the “Lead Engagement” field that shows the result of the FORMUNIXTIME function.

Let’s understand it with the example:

  • Suppose we have created a lead with the lead external timestamp “1711872000”. After the execution of the formula, we will get the output for the lead engagement “3/31/2024, 1:00 AM”.
FORMUNIXTIME Function in Salesforce Lightning Example

With this, we have learned how to use the FORMUNIXTIME function in Salesforce Lightning. Now, we will proceed and learn to use the FORMUNIXTIME function in Salesforce Classic.

How FORMUNIXTIME function used in Formula Field in Salesforce Classic

Let’s understand, with the scenario, how to use the FORMUNIXTIME function in the formula field in Salesforce Classic.

Scenario: Suppose we have two Unix timestamps in Salesforce representing the start and end time of the campaign, and we want to calculate the duration of the campaign.

1. Login into the Salesforce account in the Classic Interface. Then, click on the “Setup” option to move to the Salesforce Classic setup page.

Salesforce Classic FROMUNIXTIME Function

2. Scroll down to the “Build” section on the left side of the page. After that, click on the “Customize” dropdown arrow and choose the object in which you want to use the function of Salesforce.

Then, click on the “Fields” option given under the object dropdown.

According to the scenario, we need to go with the “Campaign” object.

Salesforce Classic FROMUNIXTIME Function Example

3. Scroll down to “Product Custom Fields & Relationships” and select the “New” button as you want to create a new field where we can convert the unix timestamp to human date and time.

FROMUNIXTIME Function in Salesforce Classic

4. In this step, pick the field type that best fits your needs. According to the scenario, we need to select the radio button next to the “Formula” field type.

Then, click on the “Next” button and move to the next step.

FROMUNIXTIME Function in Salesforce Classic Example

5. First, fill in the “Field Label,” and once you click on the “Field Name,” it fills in automatically. As per the scenario, we can fill the field label as “Campaign Duration”.

After that, scroll down to the “Formula Return Type” section and choose the return type for the formula field. According to the scenario, we need to choose “Number” as the return data type.

If we want to reset the “Decimal Places”, we can set it according to the needs. Now, click on the “Next” button to move to the next step.

How FORMUNIXTIME function used in Formula Field in Salesforce Classic

6. In this step, we are on the “Enter the Formula” page. Now, from the “Functions” section, click on the “All Functions Categories” dropdown and choose “Date Time” from the dropdown menu.

After we click on the date and time category, we will get the list of all the functions that are of date and time type; from the list of functions, search for the FORMUNIXTIME function and click on it.

Once you click it, you will get a button “Insert Selected Function” under the function box. With this, the function gets inserted into the advanced formula subtab, and the function parameter with the appropriate text, fields, etc., as per the requirement.

As per the scenario, we need to calculate the duration of the campaign in the hour where the time is represented in the Unix timestamps, and the final result is in the human read date and time.

See also  Salesforce VALUE Function

The formula is as given below:

IF(
  NOT(ISNULL(Campaign_Start_Timestamp__c)) && NOT(ISNULL(Campaign_End_Timestamp__c)),
  ROUND((FROMUNIXTIME(Campaign_End_Timestamp__c) - FROMUNIXTIME(Campaign_Start_Timestamp__c))*24, 2),
  NULL
)

Here is a detailed explanation of the formula:

  • IF Function: Here, we use the IF function that checks if both start and end timestamps are not null. If true, it returns the result duration of the campaign in an hour. Otherwise, it returns NULL.
  • ISNULL(Campaign_Start_Timestamp__c): Here, we check if the field Campaign_Start_Timestamp__c is null.
  • NOT(ISNULL(Campaign_Start_Timestamp__c)): It takes the result from the above and negates it. So, it returns FALSE if Campaign_Start_Timestamp__c is null and TRUE otherwise.
  • ISNULL(Campaign_End_Timestamp__c): Here, we check if the field Campaign_End_Timestamp__c is null.
  • NOT(ISNULL(Campaign_End_Timestamp__c)): It takes the result from the above and negates it. So, it returns FALSE if Campaign_End_Timestamp__c is null and TRUE otherwise.
  • && Logical operator: Here we combine NOT(ISNULL(Campaign_Start_Timestamp__c)) && NOT(ISNULL(Campaign_End_Timestamp__c)). It returns TRUE only if both are not null.
  • FROMUNIXTIME(Campaign_End_Timestamp__c): Converts the Unix timestamp in the field Campaign_End_Timestamp__c to a Salesforce datetime value.
  • FROMUNIXTIME(Campaign_Start_Timestamp__c): Converts the Unix timestamp in the field Campaign_Start_Timestamp__c to a Salesforce datetime value.
  • – operator: It calculates the time difference between the end and start timestamps. This result is in Salesforce datetime format.
  • ROUND((FROMUNIXTIME(Campaign_End_Timestamp__c) – FROMUNIXTIME(Campaign_Start_Timestamp__c))*24, 2): Here, we multiply the time difference by 24 to convert it from days to hours. Then, the ROUND function rounds the result of two decimal places.

Click on the “Check Syntax” button to check whether there is any error in the formula. If there is no error, you will get the success message; otherwise, you will get the error message.

Additionally, we can enter the “Description” and “Help Text” if we want. After that, click on the “Next” button to move to the next step.

How FORMUNIXTIME function used in Formula Field in Salesforce Classic Example

7. Now, select the profiles to which you want to grant edit access to this field via field-level security. The field will be hidden from all profiles if you do not add field-level security.

Then click on the “Next” button to move.

How FORMUNIXTIME function used in Formula Field in Salesforce

8. Choose the page layout that should include the field. The field will not appear on any pages if you do not select a layout.

When finished, click on the “Save” button to complete the process of the formula field in Salesforce Classic.

How FORMUNIXTIME function used in Formula Field in Salesforce Example

Once the formula field is created, you can use the formula field. Let’s see an example:

  • Open the “Campaign” object, create a new campaign with the fields “Campaign Start Unix Timestamp” and “Campaign End Unix Timestamp”, and save it.
  • Move to the campaign detail page, and we will see the “Campaign Duration” field showing the result of the function.

Let’s understand the example in detail.

  • Suppose you have created a campaign with the campaign start unix timestamp “1707072000” (2/4/2024, 10:40 AM) and the campaign end unix timestamp “1707158400” (2/5/2024, 10:40 AM) and save it.
  • The formula will execute and show the Campaign Duration as “24”.
Example of FROMUNIXTIME Function in Salesforce

With this, we have learned how to use the FORMUNIXTIME function in Salesforce Classic.

Conclusion

I hope now you have a clear knowledge of the FORMUNIXTIME function in Salesforce. I have provided detailed information about the function, its syntax, use case, example, etc.

After going through this article, you will become confident in how to use the FORMUNIXTIME function in Salesforce Lightning and Salesforce Classic in the formula field as per your needs.

You may like to read: