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?

  • 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
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

  • 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 '16 at 14:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.