# Convert TEXT(“56:04”,“[h]:mm”) into a usable Hours value

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:

``````=TEXT("56:04","[h]:mm")
``````

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 `#DIV/0!` error.

I tried copying this value into another cell using the below formula:

``````=TIMEVALUE(J2)
``````

...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?

• you can simple put `--` in front of the first equaiton: `=--TEXT("56:04","[h]:mm")` then format that cell to `[h]:mm`. But that asks the question as to why you are converting it to text in the first place and not simply entering `56:04` in the cell that is custom formatted [h]:mm? – Scott Craner Dec 13 '16 at 14:20
• Thanks, guess I did not explain myself quite clearly enough. I'm extracting data on a monthly basis from our helpdesk system and dumping it into a Excel template I'm building to automate various calculations. The `=TIME("XX:YY","[h]:mm")` format the cell is in is just the format the data is extracted in from the helpdesk system. It's not something I'm manually keying in. – Rawns Dec 13 '16 at 14:23

`TIMEVALUE` (as it's suggested by it's name) converts only time portion of the string to number, and it ignores number, so for correct conversion you need to sum it with `DATEVALUE`:
• Thanks so much. That's done the trick. I compacted it down slightly by replacing my `=TIMEVALUE(J2)` with `=DATEVALUE(J2)+TIMEVALUE(J2)` and my calculations now report a full 56 hrs 4 mins instead of 8 hrs 4 mins. :) – Rawns Dec 13 '16 at 14:36