2

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")

Screenshot

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?

Improve this question
2
  • 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, 2016 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, 2016 at 14:23

1 Answer 1

Reset to default
1

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:

enter image description here

Improve this answer
1
  • 1
    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, 2016 at 14:36

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .