Data type conversion

The following matrix is designed to give you a quick look at the feasibility of data type conversion of a value from one data type to another.

Conversion from Numeric to Text

To convert a number to text, use the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

• Format([Numeric Column]) converts the numeric value stored in the "Numeric Column" to a text value.
• ToText(5+5) returns "10.000000".

Conversion from Numeric to Duration

To convert a number into a duration, utilize the Weeks, Days, Hours, Minutes, or Seconds function. If the number represents a month or a year, conversion into a duration is not possible.

For instance:

• Weeks(1) - returns a duration of 1 week.
• Days(1) - returns a duration of 1 day.
• Hours(3) - returns a duration of 3 hours.
• Minutes(10) - returns a duration of 10 minutes.
• Seconds(60) - returns a duration of 60 seconds, equivalent to 1 minute.
• Weeks([Numeric column]) - converts the value from the "Numeric column" into a duration in weeks.
• Days([Numeric column]) - converts the value from the "Numeric column" into a duration in days.
• Hours([Numeric column]) - converts the value from the "Numeric column" into a duration in hours.
• Minutes([Numeric column]) - converts the value from the "Numeric column" into a duration in minutes.
• Seconds([Numeric column]) - converts the value from the "Numeric column" into a duration in seconds.

If the number is a constant, you can simply add a "w", "d", "h", "m", or "s" suffix at the end to represent the duration in weeks, days, hours, minutes, or seconds, respectively.

For example:

• 3w - returns a duration of 3 weeks.
• 2d - returns a duration of 2 days.
• 1h - returns a duration of 1 hour.
• 25m - returns a duration of 25 minutes.
• 120s - returns a duration of 120 seconds, which is equivalent to 2 minutes.

Conversion from Numeric to Location

To convert a number to a location, use the ToLocation function.

For example:

• ToLocation(35.658068, 139.751599) returns the location for Tokyo.
• ToLocation([Latitude Column], [Longitude Column]) converts the values from the "Latitude column" and the "Longitude Column" into a location.

Conversion from Numeric to Boolean

To convert a number into a boolean, use the ToBoolean function.

For instance:

• ToBoolean(1) returns true.
• ToBoolean(0) returns false.
• ToBoolean([Numeric Column]) returns true if the value from the "Numeric Column" is a non-zero value; otherwise, it returns false.

If the numeric is a constant, you can simply convert it to the boolean literal by replacing the zero value with false and any other value with true.

Conversion from Text to Numeric

To convert text into a number, use the ToNumber function.

For example:

• ToNumber("123.56") returns the numeric value 123.56.
• ToNumber("345,98", "de-DE") returns the numeric value 345.98.
• ToNumber([Text column]) converts the value from the "Text column" into a number.

If the text is a constant, you can simply convert it to the numeric literal. Numeric values should be written as is without the quotes. For example, 123.56.

Conversion from Text to Date

To convert text into a date, utilize the ToDate function.

For example:

• ToDate("2012-01-15") returns the date value #2012-01-15#.
• ToDate("Monday, 13 December 2010", "en-US") returns the date value #2010-12-13#.
• ToDate("08/05/2024", "fr-FR") returns the date value #2024-05-08#.
• ToDate([Text column]) converts the value from the "Text column" into a date.

If the text is a constant, you can simply convert it to the date literal. The date values should be written in the year-month-day format using dashes as separators and enclosed in pound signs #. For example, #2009-10-30#.

Conversion from Text to Time

To convert text into a time, utilize the ToTimeOfDay function.

For example:

• ToTimeOfDay("22:00") returns the time value #22:00#.
• ToTimeOfDay("12:03:29") returns the time value #12:03:29#.
• ToTimeOfDay("3:04 pm", "en-US") returns the time value #15:04#.
• ToTimeOfDay([Text column]) converts the value from the "Text column" into a time.

If the text is a constant, you can simply convert it to the time literal. The time values should be written using the 24-hour clock, with a colon as the separator, and enclosed in pound signs #. For example, #23:30# or #09:15:30#.

Conversion from Text to User

To convert text into a user, utilize the ToUser function. The text should be the specific user’s email address.

For example:

• ToUser("linda.smith@abc.com") returns the user with the email address linda.smith@abc.com.
• ToUser([Text column]) converts the value from the "Text column" into a user.

Conversion from Text to Location

To convert text into a location, use the ToLocation function.

For example:

• ToLocation("35.658068, 139.751599") returns the location for Tokyo.
• ToLocation([Text Column]) converts the latitude and longitude values from the "Text column" into a location.

Conversion from Text to Boolean

To convert text into a boolean, utilize the ToBoolean function. The values "1", "true", or "yes" are converted to true; other values are converted to false. Case is ignored.

For example:

• ToBoolean("1") returns true.
• ToBoolean("YES") returns true.
• ToBoolean("text") returns false.
• ToBoolean([Text column]) converts the value from the "Text column" into a boolean.

If the text is a constant, you can simply convert it to the boolean literal without using any function for that.

Conversion from Date to Text

To convert a date to text, use the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

• Format([Date Column]) converts the date value stored in the "Date Column" to a text value.
• ToText(#2013-03-25#) returns a date as the text type value "2013-03-25".

Conversion from Date to Timestamp

To convert a date to a timestamp, use the ToTimestamp function which returns a timestamp that corresponds to 12:00 am of the given date in the local time zone (midnight at the beginning of the date).

For example:

• ToTimestamp(#2013-04-30#) returns the timestamp value "4/30/2013 12:00:00 AM".
• ToTimestamp([Date Column]) converts the date value stored in the "Date Column" to a timestamp value.

Conversion from Time to Text

To convert a time to text, use the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

• Format([Time Column]) converts the time value stored in the "Time Column" to a text value.
• ToText(#10:15:00#) returns a time as the text type value "10:15:00".

Conversion from Time to Timestamp

To convert a time to a timestamp, use the ToTimestamp function, which returns a timestamp that corresponds to the given time on the given date in the local time zone.

For example:

• ToTimestamp(#2013-04-30#, #22:10:00#) returns the timestamp value 4/30/2013 10:10:00 PM.
• ToTimestamp(Today(), [Time Column]) converts the time value stored in the "Time Column" to a timestamp value for today.

Conversion from Timestamp to Text

To convert a timestamp to text, use the Format or ToText function. If you need to convert a column value, use the Format function.

For example:

• Format([Timestamp Column]) converts the timestamp value stored in the "Timestamp Column" to a text value.
• ToText(#2013-03-25 10:51:34#) returns a timestamp as the text type value "2013-03-25 10:51:34".

Conversion from Timestamp to Date

To convert a timestamp to a date, use the ToDate function, which returns the date, in the local time zone, in which the timestamp falls.

For example:

• ToDate(#2013-04-30 22:10:00#) returns the date value 4/30/2013.
• ToDate([Timestamp Column]) converts the timestamp value stored in the "Timestamp Column" to a date value.

Conversion from Timestamp to Time

To convert a timestamp to a time, use the ToTimeOfDay function, which returns the time, in the local time zone, corresponding to the timestamp.

For example:

• ToTimeOfDay(#2013-04-30 22:10:00#) returns the time value 10:10 PM.
• ToTimeOfDay([Timestamp Column]) converts the timestamp value stored in the "Timestamp Column" to a time value.

Conversion from Duration to Numeric

To convert a duration into a number, utilize the ToWeeks, ToDays, ToHours, ToMinutes, or ToSeconds function.

For example:

• ToWeeks(7d) - returns a numeric value of 1.
• ToDays(1d) - returns a numeric value of 1.
• ToHours(1d) - returns a numeric value of 24.
• ToMinutes(1h) - returns a numeric value of 60.
• ToSeconds(5m) - returns a numeric value of 300.
• ToWeeks([Duration column]) - converts the value from the "Duration column" into a number of weeks.
• ToDays([Duration column]) - converts the value from the "Duration column" into a number of days.
• ToHours([Duration column]) - converts the value from the "Duration column" into a number of hours.
• ToMinutes([Duration column]) - converts the value from the "Duration column" into a number of minutes.
• ToSeconds([Duration column]) - converts the value from the "Duration column" into a number of seconds.

Conversion from Duration to Text

To convert a duration to text, use the ToText function.

For example:

• ToText([Duration Column]) converts the duration value stored in the "Duration Column" to a text value.
• ToText(1h) returns a duration as the text type value "01:00:00".

Conversion from User to Text

To convert a user to text, use the UserToName or UserToEmail function.

For example:

• UserToName([Created By]) returns "John Doe" for a record created by the user John Doe.
• UserToEmail([Last Modified By]) returns the email address of the user who last modified a record.

Conversion from Location to Numeric

To convert a location to a number, use the Latitude or Longitude function.

For example:

• Latitude(ToLocation(35.658068, 139.751599)) returns the numeric value 35.658068, which represents the latitude.
• Latitude([Location Column]) returns the numeric value of the latitude stored in the "Location Column".
• Longitude(ToLocation(35.658068, 139.751599)) returns the numeric value 139.751599, which represents the longitude.
• Longitude([Location Column]) returns the numeric value of the longitude stored in the "Location Column".

Conversion from Boolean to Numeric

To convert a boolean into a number, utilize the ToNumber function which returns 0 if the boolean value is false, and 1 if the boolean value is true.

For example:

• ToNumber(true) returns 1.
• ToNumber(false) returns 0.
• ToNumber([Boolean Column]) converts the boolean value stored in the "Boolean Column" to a numeric value.

Conversion from Boolean to Text

To convert a boolean to text, use the ToText function.

For example:

• ToText([Boolean Column]) converts the boolean value stored in the "Boolean Column" to a text value.
• ToText(true) returns "1".
• ToText(false) returns "0".