Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-09-24
5
Medium Priority
?
111 Views
Last Modified: 2014-09-24
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
0
Comment
Question by:Jorgen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341319
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 40341327
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
 
LVL 4

Author Closing Comment

by:Jorgen
ID: 40341476
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40341520
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
 
LVL 4

Author Comment

by:Jorgen
ID: 40342454
Works as well, and always nicer with shorter formulas.

Regards

Jørgen
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question