Convert Text to Datetime
Datablist uses format strings from Unicode Technical Standard.
Here is a summary of useful datetime format letters:
d - The day of the month from 1 through 31 dd - The day of the month from 01 through 31 M - The month, from 1 through 12 MM - The month, from 01 through 12 yy - The year from 00 to 99 yyyy - The year as a four digit number h - The hour using 12-hour clock from 1 - 12 hh - The hour using 12-hour clock from 01 - 12 H - The hour using 24-hour clock from 0 - 24 HH - The hour using 24-hour clock from 00 - 24 m - The minute from 0 to 59 mm - The minute from 00 to 59 s - The second, from 0 through 59 ss - The second, from 00 through 59
- Both Month and Minutes start with the letter m. Beware that to differentiate them, we use a uppercase M for months, and a lettercase m for minutes.
- D and DD that represent the day of a year (1, 2, ..., 365, 366) are often confused with d and dd that represent the day of a month (1, 2, ..., 31).
- YY and YYYY that represent the local week-numbering year (44, 01, 00, 17) are often confused with yy and yyyy that represent the calendar year.
Example of dates and formats:
2/5/2023 13:34:11 is M/d/yyyy HH:mm:ss
February 5, 2023 is MMMM d, yyyy
Sunday, June 5, 2013 is EEEE, MMMM d, yyyy
Convert Unix Timestamp to DateTime
Another format to represent a DateTime is the Epoch Unix Timestamp. A timestamp is the number of seconds between the Unix Epoch (00:00:00 UTC on 1 January 1970) and a particular date.
Datablist lets you convert Unix Timestamps from a Text or Number property to a DateTime property.
A timestamp usually represents a number of seconds. But now we see values with higher granularity, for example, timestamps in milliseconds.
Datablist automatically detects if the timestamps are in seconds or milliseconds.
Convert Text to Number
The Text to Number converter searches for digits in the Text and return an Integer or a Float number.
Separators for decimals and thousands differ with languages.
1.000,00 are valid numbers depending if your are in France or in the US.
With the Text to Number converted, you define the format and get rid of the ambiguity to have structured number values.
If your numbers don't have decimals, the Decimal Separator option won't impact the results.
Extract number from text with letters
Running the Text to Number converter on text with both digit and letter will remove the letters to keep only the digits.
1000 USD will be converted to
If the text contains several numbers, only the first one is kept.
1000 USD with a discount of 50 will be converted to
Convert Number to Text
The opposite conversion, Number to Text, is also available. This is useful when you have to concatenate your numbers with a Text property.
Convert Text to Checkbox (Boolean)
Use the convert Text to Checkbox tool to create a Boolean property with True and False values.
You only have to set the text values that must be converted to "True". All other values will be "False".
- Values will be transformed to lowercase before process. No need to write the values with case variations.
- Spaces are removed both from the data and from the conversion values