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
LVL 4
JorgenAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Also shorter formula for you for dealing with 5 or 6 digits:

=TIMEVALUE(LEFT(TEXT(C2,"000000"),2)&":"&MID(TEXT(C2,"000000"),3,2)&":"&RIGHT(TEXT(C2,"000000"),2))

The TEXT(C2,"000000") forces it to 6 digits if it isn't already.

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
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
0
 
JorgenAuthor Commented:
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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
JorgenAuthor Commented:
Works as well, and always nicer with shorter formulas.

Regards

Jørgen
0
All Courses

From novice to tech pro — start learning today.