When exporting time entries from PSOhub using the Hours report (Time & Expenses > Hours), certain fields are formatted as text. This prevents you from using Excel formulae such as SUM(), or AVERAGE() unless you first convert those fields into number format.
This article will walk you through how to:
-
Convert the Hours column into number format, and
-
Format columns containing currency symbols (€, £, $, CHF) so you can perform calculations
Convert Hours from Text to Numbers
1. Open the Excel file with the exported hours
2. Locate the Hours column in the spreadsheet
3. Select the entire column by clicking the column header.
4. In the top menu, navigate to the Data tab
5. Click Text to Columns in the Data Tools section. This will open the Convert Text to Columns wizard.
6. In the first step, choose Delimited and click Next
7. Uncheck all Delimiter options and click Next.
8. In the final step, ensure General is selected under Column Data Format and click Finish.
The Hours column is now converted to numeric format, and you can use formulae such as SUM() or AVERAGE().
Convert Currency Fields to Numbers
Important: If using multiple currencies on your PSOhub account, it's important to convert each currency separately to avoid mixing different currencies in a single calculation.
Filter for a specific currency symbol
1. Select any cell in the report.
2. Apply filters by clicking Data > Filter
3. Select the column containing the currency symbol you'd like to compute
4. Apply a custom filter for the currency symbol you want to convert.(e.g. €, £, $, CHF). This will filter the time entries for this currency only
Format as currency
1. Select the column containing the currency symbol again
2. Right-click and choose Format Cells
3. Go to the Currency category
4. Choose the appropriate currency symbol
5. Click OK
Reformat the values using Find & Replace
1. With the same cells selected, go to the Home tab
2. Go to Find & Select > Replace
3. In the Find what field, type the currency symbol and a space (e.g. € ).
4. Leave the Replace with field empty
5. Click Replace All
Repeat all of these steps for each currency (€, £, CHF) separately.
Once replaced, Excel will treat those values as numbers and you can now use formulae like SUM() or AVERAGE().