Statistics are a very important element for a business and for understanding your customers. Sometimes, you have to rework the data via Excel, in order to create pivot tables (for instance) or to analyse the data.
After exporting statistics, you can download a standard Excel file .xls or xlsx where the data are already in separate columns but all in standard format. This is basically a conversion of .csv file
In order to be able to use the data as date, text or number, you must convert the data from standard format into text format at first and then to other format.
How to convert standard data to text format?
In order to convert data from standard format into text format, which is easier to use, you must:
1. Select the column
2. Choose the automatic Excel data converter (Data / Text in table)
3. Specify the text format as the nature of the data
The nature of your data is now in text format (per default) and you can check it on the following table:
How to convert data to numeric format?
Once your data is in text format, it's easier to convert it into other formats via Excel formulas.
This is the case of conversion to numbers : you can use the pre-defined formula Replace (Crt + H).
This is the case of conversion to numbers : you can use the pre-defined formula Replace (Crt + H).
1. Select the column
2. use the pre-defined formula Replace by typing Crt + H
3. Then simply replace in this case the separators "." with separators "," as in the example below.
Apply the replacement to all (all elements of the selected column).
The nature of the data is automatically converted to a numeric format.
The nature of the data is automatically converted to a numeric format.
Then it's became possible to calculate with the data of this column: you can add, subtract, sum, ...
How to convert data to Date format?
Once your data is in text format, it's easier to convert into other formats via Excel formulas.
This is the case with date conversion, but it requires a few steps.
This is the case with date conversion, but it requires a few steps.
1. Insert a column after the column to be converted into Dates format
2. Insert the following formula in the first cell of this column
Convert the text cells (example'12/06/2018') of your Excel extraction into the date format, using the formula Date(year, month, day):
- Year = 2018 as simple or Right(text cell;4) where text cell is the cell to be transformed into Date
- Month= Right(Left (text cell;5);2) where text cell is the cell to be transformed into Date
- Day= Left(text cell;2) where text cell is the cell to be transformed into Date
Indeed you create then the formula:
Date(right(text cell;4); right(left(text cell;5);2);left(text cell;2))
- Year = 2018 as simple or Right(text cell;4) where text cell is the cell to be transformed into Date
- Month= Right(Left (text cell;5);2) where text cell is the cell to be transformed into Date
- Day= Left(text cell;2) where text cell is the cell to be transformed into Date
Indeed you create then the formula:
Date(right(text cell;4); right(left(text cell;5);2);left(text cell;2))
3. Stretch this cell over the entire column to convert the entire column into Dates
Note:
1. You can then copy and paste value in order to stabilize this data and work easier with it.
2. You can specify the format of the cell as Date to issue them automatically in the chosen version ((2018-06-12 in FR or 12-06-2018 in EN).
2. You can specify the format of the cell as Date to issue them automatically in the chosen version ((2018-06-12 in FR or 12-06-2018 in EN).
How to calculate intervals of Dates?
Once your data is in text format, it's easy to calculate intervals (for example) or apply formulas. This also requires few steps.
1. Insert a column after the column to be converted into Dates format
2. Insert the following formula in the first cell of this column
Calculate a range of dates by the formula
=DATEDIF(DATE(2018;Left(B2;2);right(left(B2;5);2));DATE(2018;LEft(C2;2);right(Left(C2;5);2));"d")
where B2 = booking date and c2 = participation date
This formula gives you the number of days between two dates.
=DATEDIF(DATE(2018;Left(B2;2);right(left(B2;5);2));DATE(2018;LEft(C2;2);right(Left(C2;5);2));"d")
where B2 = booking date and c2 = participation date
This formula gives you the number of days between two dates.