I'm extracting data from a piece of helpdesk software in XLS format. One of the values extracted is a time value in hours and minutes. It appears in the exported XLS file like so:
I'm trying to analyse this data (to work out things like average time, if it exceeded X hours etc) but in the format above, Excel does not seem to recognize it as a valid time as if I try and use an
AVERAGE formula for a range of cells with the above values in, I get a
I tried copying this value into another cell using the below formula:
...and I formatted the cell to a custom time value in [h]:mm format.
AVERAGE formulas do work with this new cell value, however I noticed that this method for the above example actually shows a value of 8.04 so is missing out a whole 48 hours, despite the fact it's formatted to use a [h]:mm format.
How can I convert this initial value correctly so Excel treats it as a correct time value that I can use for analysis?