asked on

How to convert a textfile to a pivottable that shows summary by the hour?

Hi Experts

I have a textfile, that I need to import to Excel, and extract specific data to a pivottable. Unfortunately I do not receive the the specification in a way, that gives me the time in a way that Works for me.

The textfile states Time as 102345 which corresponds to 10:23:45.
I thought I could convert that by using this formula:
=IF(LEN(C4)=5;LEFT(C4;1)&":"&MID(C4;2;2)&":"&RIGHT(C4;2);LEFT(C4;2)&":"&MID(C4;3;2)&":"&RIGHT(C4;2)) and format this to a time format.

In my import it seems to Work fine, but when I import to a pivottable and wants to Group by the hour, I get the error that it is not possible to Group these data.

I have the time from the text file in column C and my calculated time in column U.

Any solutions on how to solve the problem, so I can get my hourly pivottable

best regards

Jørgen
Importtest.xlsx

Rob Henson

Your formula is converting the time to a string of text that happens to look like a time. Enclose the formula in a TIMEVALUE formula:

=TIMEVALUE(IF(LEN(C4)=5;LEFT(C4;1)&":"&MID(C4;2;2)&":"&RIGHT(C4;2);LEFT(C4;2)&":"&MID(C4;3;2)&":"&RIGHT(C4;2)))

This will then convert it to a true time.

Thanks
Rob H

ASKER CERTIFIED SOLUTION

Rob Henson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jorgen

ASKER

Hi Rob,

It just worked exactly like I wanted it to. I just generated a report in 5 minutes, that we have been waiting for in the last couple of years

Thanks

Jørgen

Rob Henson

Even shorter still using only numeric functions:

=TIME(INT(C2/10000),INT((C2-INT(C2/10000)*10000)/100),C2-FLOOR(C2,100))

Thanks
Rob H

Jorgen

ASKER

Works as well, and always nicer with shorter formulas.

Regards

Jørgen