HCE Standard
HCE Standard
Breadcrumbs

Converting Exported Fields

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")

    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