Features

Exported CSV from FYI displays time as a decimal, how do I see it as time?

Issue

When exporting reports from FYI, the Time is displayed in a decimal format. How do I see it as hours and minutes?

Solution

Displaying the time in a decimal allows for calculations for better reporting, for example, total time, or capacity.

However, depending on their requirements, practices may need to display time using the h:mm format instead.

To change the displayed time into the hour and minutes format:

  1. In Microsoft Excel, add a heading to a blank column. In this example, we are using column K to display the "Actual" hours in hours and minutes, and enter the heading "Actual (h:mm)".
    3107_Excel_New_Column_Actual_Hours.gif
  2. Click on the Formula bar and enter the formula below:

    =(cell)/24

    where (cell) is the cell with the value we want to convert. In the example below, the cell is I2. 
    3108_Excel_Formula_Actual_Hours_24.gif
  3. A decimal value will be shown - this will shortly be converted into the required format of h:mm.

  4. Select the cell with the formula entered in step 2.

  5. Hover over the corner of the cell until the icon changes to a + symbol, and double-click to add the formula to all cells in the column. 
    Tip: You can also copy/paste the cell to all of column K to add the formula for those cells.
    3109_Excel_apply_formula_all_cells.gif
  6. Click on the column header to select the entire column. For example, click on the letter K to select all of column K.

  7. Right-click on the highlighted column and select Format Cells.

  8. On the Number tab, select the category of "Custom".

  9. In the Type list, scroll down and select "h:mm".
    3110_Excel_Format_Cells_hmm.gif
  10. Click OK.

  11. The column will be updated to display the time in the h:mm format.
    3111_Excel_Formatted_Cells_hmm.gif

Note: Xero Practice Manager can also be configured to display reports using a decimal (for comparing and reconciling data). 

Was this article helpful?
0 out of 0 found this helpful