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?
--
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 entering56:04
in the cell that is custom formatted [h]:mm?=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.