Convert Text to DateTime
Datablist provides an easy to use tool to parse text and convert it as DateTime. This tool can be used to convert text to simple dates as well. For Dates, the hours, minutes, seconds, etc. are set to zero.
Datablist uses format strings from Unicode Technical Standard.
Here is a summary of useful DateTime format patterns:
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
Notes
- 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 which represent the day of a year (1, 2, ..., 365, 366) are often confused with d and dd which represent the day of a month (1, 2, ..., 31).
- YY and YYYY which represent the local week-numbering year (44, 01, 00, 17) are often confused with yy and yyyy which 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.
Normalize separators
Separators for decimals and thousands differ with languages.
Both 1,000.00 and 1.000,00 are valid numbers depending if you 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 numbers from text with letters
Running the Text to Number converter on text with both digits and letters will remove the letters to keep only the digits.
1000 USD will be converted to 1000.
If the text contains several numbers, only the first one is kept.
1000 USD with a discount of 50 will be converted to 1000.
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".
Notes:
- Values will be transformed to lowercase before the process. No need to write the values with case variations.
- Spaces are removed both from the data and from the conversion values
Convert DateTime to Text
You can convert DateTime values to texts using the same patterns shown for text to DateTime. You can add extra text that will be kept during the conversion using single quotes.
For example, with the format:
'Extra text:' dd-MM-yyyy
DateTime values will be converted into text such as Extra text: 12-12-2022
