Consolidating time in Pivot Table for tallying and graphing purposes

[Thomaswu6]

Hello there!

I am trying to plot a graph with 4,478,533 lines of data. (A lot, I know!) Seeing how it goes beyond the excel limit, I split it into 5 smaller files, so approximately 1 million line each. The data are essentially epoch timestamps, and I am trying to make a graph showing a cumulative tally against time.

Here is what my raw data looks like.
Capture.PNG

Using an epoch equation I found, I managed to extract a date in "normal" form.
Capture2.PNG

Using a pivot table, I managed to get some sort of tally count.
Capture3.PNG

The problem here is that the data are too spread out. As you can see from the screenshot, although all of the cells has 30/7/01 in it, it is still separated by the differences in minutes/seconds which isn't shown. Is there any way where I can consolidate these times into larger chucks? Preferably 3 hours intervals, but I'm fine with 1 day intervals too.


I have uploaded an extract of the spreadsheet I'm working on as the original is too big (90Mb!) for upload. Not sure if that'll help or not.
sample_excelforum_coderedaugust.xlsx

Is it possible to accomplish what I am trying to do in excel? Thank you again!

[cbatrody]

Hi,

Welcome to the Forum.

You can use INT(DATE field) for day-wise summary(file attached)

[Thomaswu6]

That worked! Thank you kind sir :)