Converting Exported Fields

Owned by Joshua Yourth
Last updated: May 13, 2020
1 min read

When exporting user data as described in Exporting User Accounts, you may wish to see the exported columns in a different format. This can be accomplished by manipulating the data in Excel or LibreOffice.

Step-by-step guides

Checkbox:

Checkboxes are stored as a 1 (for checked) or a 0 (for unchecked). Follow these steps to convert to TRUE or FALSE

  1. After opening your csv file in Excel or LibreOffice, use the following command in a new column. Cell S2 is assumed to be the checkbox cell.

    Excel or LibreOffice

    =IF(S2 = 0, "False", "True")

    Tip

    You can also replace "False" and "True" from the command above with "No" and "Yes" depending on your preference.

  2. Use Autocomplete to populate the rest of the column.


Date:

Date field's are stored in UNIX time. Follow these steps to convert a date field.

  1. After opening your csv file in Excel or LibreOffice, use the following command in a new column. Cell T2 is assumed to be the date cell.

    Excel or LibreOffice

    =(((T2/60)/60/24)+DATE(1970,1,1))

  2. Use Autocomplete to populate the rest of the column.


Microsoft Excel cell formatting: Format cells

LibreOffice cell formatting:  Formatting data