Converting Text or Numeric Time Values to Dates- FineBI Document

Last update： 2023-06-16

Overview

Application Scenario

There are various formats for you to record time, but some of which are stored as text fields or numeric fields. Sometimes it is necessary to parse strings or numeric fields into dates.

Solution

Add a TODATE Function or a DATE Function in Formula Column to convert text or numeric fields into time fields.

Converting Text Fields to Dates

Sample data: Text Field.zip

1. Click New Subject, and add the data in sample Text Field.xlsx.

2. The fields needed for conversion including 2020/05/22, 2020-05-22, 20200522, and 202005. If the uploaded fields are recognized as other field types, you can manually change them back to text type.

3. Click Formula Column, name it Date Type1, enter formula TODATE(Text Field 1) to convert Text Field 1 into a time field, and click OK.

Note:

Note: Functions and fields in the box cannot be manually entered, and you need to click options on the left.

Similarly, create new columns and enter the formulae TODATE(Text Field 2), TODATE(Text Field 3, "yyyyMMdd"), and TODATE(Text Field 4, "yyyyMM") respectively.

Note:

Text Field 4 only contains year and month, so after it has been converted to date type, it defaults to the first day of each month.

For details about usage of the TODATE function, see TODATE.

Converting Numeric Fields to Dates

Sample data: Numeric Field.zip

The format of the fields being converted is shown below:

Y/M/D	Year	Month	A
20200522	2020	5	1,590,727,781,511

Converting Fields in a Single Column to Dates

Configure Field Settings for the Y/M/D field first. Convert it to a text field, and then convert it to date type.

Convert the A field to date type.

Converting Multiple Columns of Values and Dates to Dates

Sometimes you may save the year, month and day in separate fields. With the DATE function, you can transform multiple fields into one date field. For details about the usage of the DATE function, see DATE.

Take Year and Month fields as an example.

1. Configure Field Settings for fields Year and Month and tick both of the two fields.

2. Click Formula Column, name the field Merge Multiple Dates, and enter formula DATE(Year,Month,22).