Need help on excel

[Grimezy]

I'm trying to get 90,000 rows of data on excel into an easy to filter pivot table. I did it easily with 2000 rows but we have some more in-depth data that's laid out a bit different.

Basically it's data from a Flow-monitoring survey. We sub-contracted it out for our client and now that he's had the final report he basically keeps coming back to us for help because he can't manipulate the data and the company we subbed it to are quite frankly useless.

My excel data has 4 columns, Date (with the time next to it), Depth, flow and intensity. Then 90,000 readings taken in a 13 week (I think) period every 2 minutes. The client wants to see this in like a daily average rather than every 2 minutes so I should technically be able to select all and then insert a pivot table and it should group all the readings from the date into the same filter and show me the average for that day. HOWEVER, my date columns are like "21/12/2012 00:02" showing the time the reading was taken at. On the 2000 row spreadsheet the times were already separated in a different column so it was easy to work around... this one is proving more difficult.

So I've tried formatting the cells into a date format that only shows the date and not the time. It worked with one but I'm fairly sure that's because the time was 00:00 so it just ignored the 0's. For the rest of them I can get it to display as the date only but the time is still there in the formula bar so my pivot table isn't grouping them like it should.

So what I'm asking you guys is is there any way I can create a formula that splits the date and times into 2 columns (kind of like the =RIGHT + =LEFT) formulas... Which I suppose I could do =LEFT and then do the right number of characters just for the date... And then hide the column with the date and time in and just use my new date one... This brainstorming malarky is brilliant..

But I'm still not sure this would be the best way, is it something where I could create a macro to delete the same things in every cell? Or is there any way to set my pivot table to filter out the times and just look at the dates?

After this rant I'm thinking the =LEFT formula could be the best bet... Why didn't I think of that earlier. Any advice that might be easier would be much appreciated!

 

[Boozad]

Couldn't you just create an extra column starting at whichever time (00:00 for example) and add the next few in. Then select the populated cells and drag down? Wouldn't that populate your times given the data that's already entered? I know you'd probably have to manually delete the times from each date cell then though...

 

[ubuysa]

You might find some help at http://www.cpearson.com/excel/MainPage.aspx.

 

[Danny_Boy]

Use the "Text to Columns" tool in the data tab. You will want to insert one or two blank columns to the right of your date column before you do this though (as it dumps the new data into the columns next to it, don't want to to lose anything already there!). Choose the delimited option for the first question box when you are in the convert text to columns wizard, then in the next one tick the Space option in the Delimiters. Click finish and it should dump everything in your date column that comes after the space into a new column so you should end up with the date in column A and the time in column B.

 

[ubuysa]

I don't use Excel, I use LibreOffice but the following works on there.

Dates & times are stored as a number in the form ddddd.tttttt, where ddddd is the number of days since 1900-Jan-0 and tttttt is the percentage of the day (so noon is 0.5).

I just input some dates and times in Time formatted cells and then reformatted the cells as numbers with 6 decimal places. They then show the serial date/time in the form ddddd.tttttt. So a simple int() function will strip out the date part into a (hidden) date only column, your pivot table could run on that?

 

[pr1s0ner]

=day() & =month() will both work with a cell formatted like that. Easy to sort and group then without using messy pivot tables

 

[Grimezy]

Thanks guys! Gonna have to give some of these a go tomorrow.

Apart from Booz's... Unless I can hire you to delete the same data from 90,000 rows? Oh and I have 3 separate sheets... We'll see you next christmas?

 

[Boozad]

I need to beat your Grid 2 times, figured that would give me time to practice while that kept you busy!

 

[Grimezy]

Aha they shouldn't be too tough, I think I had it on hard though so to win the race I had to go pretty well, I wasn't just winning them first time.

Got a bit bored after a bit though :\ Don't think the career format really agreed with me. Multiplayer is good though, gets the heart pumping!

 

[Boozad]

I only have ten minutes on it here and there, I like racing games but I can't play them for long at all before getting bored.

 

[Grimezy]

Just an update, the text to column thing worked a treat so thanks for that! Had no idea it existed.

And it's a bit of a weird career Booz, you can't buy cars or upgrade them, but you can online... On singleplayer they just gift you cars every couple of races and I find it far too dull and arcadey. Bring on Forza 5...

 

[Boozad]

And it's a bit of a weird career Booz, you can't buy cars or upgrade them, but you can online... On singleplayer they just gift you cars every couple of races and I find it far too dull and arcadey. Bring on Forza 5...

Yeah I'm not overly impressed by it, while the actual racing is good the career mode is dull and uninspiring. I can't even be bothered to paint the cars and change the sponsors and stuff, I just move on to the next race. DiRT 3 is pretty much the same too trying to direct you to buy DLC which turns me off a game more than anything. It's fine if it's optional extras you go looking for, but when it sticks loads of greyed out boxes in the main game telling you to buy them I just turn it off.