Formatting Date, Time, and Numeric Values in Make
Make is a powerful automation platform that allows users to connect different apps and services to automate various tasks. By enabling seamless data transfer, it increases productivity and enhances business processes. However, handling formatting between different systems can pose challenges.
In Make, users can format date, time, and numeric values using various modules and functions. The platform supports various formatting options for each type of value, allowing users to customize the output according to their needs. In the next sections, we will show how to properly format date, time, and numeric values, to ensure that the SpreadsheetWeb Hub automations are efficient, accurate, and user-friendly.
Date Time
Different systems may employ varying date and time formats, which can lead to inconsistencies.. Make offers various modules and functions for manipulating date and time values, allowing users to easily add or subtract time, format date and time values, convert time zones, and more. Using Make in conjunction with SpreadsheetWeb Hub, users can easily format date and time data to meet their specific needs, saving time and effort while ensuring accuracy and consistency across different platforms.
Automatic parsing
Make can handle many date-time values automatically if the item data type is set as date or time. You can see a parameter’s, a named range for Make’s SpreadsheetWeb Hub app, data type by the icon appears when you click its box.
If you see another icon like text (A) or number (#), check the corresponding named range in your Excel file and change its formatting setting to date/time if the formatting is something else.
After updating and publishing your app with the new formatting, you will see the correct data type in Make.
Invalid date
The “invalid date” error means that Make can’t parse the date/time value from previous step.
To handle this scenario, you need to provide the pattern of the source data with Make’s parseDate function. The function takes three arguments. Since the third is optional, the source date/time and the pattern are enough.
parseDate (text; format; [timezone])
text | Source date/time |
format | Pattern similar Excel’s number formatting.
e.g., DD.MM.YYYY hh:mm where D is day, M is month and so on. |
[timezone] |
You can learn more about Make’s parseDate function in Date/Time functions.
#VALUE! Error
#VALUE! error in Excel occurs when you want to perform a mathematical operation on a text.
If you do not want to make changes on the Excel file, you can modify the source date/time value in a desired format by using Make’s formatDate function.
formatDate (text; format; [timezone])
text | Source date/time |
format | Pattern similar Excel’s number formatting.
e.g., DD.MM.YYYY hh:mm where D is day, M is month and so on. |
[timezone] |
You can learn more about Make’s formatDate function in Date/Time functions.
Currency
In SpreadsheetWeb Hub Apps, you have access to different built-in functions and formatting options that allow them to adjust the currency symbol, decimal points, and thousands of separators to suit their specific needs. Formatting currency correctly can assist users in creating clear and easily understandable financial documents, spreadsheets, and applications. This can lead to better decision-making and guarantee that financial information is presented accurately. The utilization of Make to format currency values in SpreadsheetWeb Hub Apps can ensure accuracy, and enhance user experience across different platforms.
Make may evaluate currency values as “text” data especially if the value contains a currency symbol like $, € or CHF. When you send this kind of value into a numeric named range in SpreadsheetWeb Hub app, you will see “invalid number” error.
To handle this kind of situation the numeric value should be stripped. You can parse the numeric value from a text by using parseNumber function.
parseNumber (number; decimal separator)
number | The string or currency value you want to parse the number within. |
decimal separator | The character that separates integer and decimal part of the number. |
After parsing the currency value, you can run your scenario without an issue.
Hundred-based percentage values
When working with data that involves percentages, it is important to format these values properly to ensure that they are accurately represented and easy to understand.
By properly formatting hundred-based percentage values, users can create clear and easy-to-understand reports and spreadsheets that help to facilitate decision-making and analysis. This can be particularly important in financial contexts, where accurate representation of data is crucial.
Some apps on Make may send percentage values with a percentage sign (%) that causes similar parsing error that currency symbols do.
You need to apply the parseNumber function to these types of values as well. However, after removing the percentage sign, the number value becomes a hundred times of the original value. The obvious workaround is to divide the number by 100. You can do that using Make’s division operator (/).
Warning: Typing slash “/” as a division operator is not working. You need to select the operator in Math functions tab specifically.
After division is applied, the correct values will be evaluated.
Formatting date, time, and numeric values correctly is essential for ensuring accurate and efficient automation in Make. By using the various modules and functions available in the platform, users can customize the formatting options to suit their needs and create meaningful timestamps, numeric values, and time values. Proper formatting not only makes the automation workflows more efficient but also enhances the user experience by displaying the data in a user-friendly format.