How to summarize a spreadsheet based on the time-of-day column

Question

I want to summarize a spreadsheet based on the time-of-day column.

Here's a link to the spreadsheet. https://www.dropbox.com/s/nepq76ylhatgbvy/electric%20power%20monitoring%20summarized%20by%20day.csv?dl=0

TIA for your help.
SweetTasha

Answer 1

What are the hour groups you want to use?

.

The simplest way to generate summary information is often a Pivot.

.

I would use PoweQuery to import the CSV.

What do you want done about spots like 12/24/2021 12:00 (2nd row in image) which is blank, 0?

Answer 2

A power spectrum analysis revealed that

there is no periodicity in your data

to group by either hour, day, week, month or year.

Instead there are predominantly periods of around 4 hours and 40 minutes

that start randomly at any hour of the day

regardless of day, week, month or year.

Used external $$ Excel add-on.

https://www.mediafire.com/file/5h0u66g18rwbsqg/03_24_22b.pdf/file

Answer 3

rohnski2

What are the hour groups you want to use?

I want to summarize based on the date. For example, with these 2 date/times

12/24/2021 23:00

12/25/2921 0:00

I want to recognize that the date has changed from 12/24 to 12/25, without respect to the 23:00 and 0:00. At that point I would sum(nnn) the Watt-Hours values). I suppose I'd need to split the values into date and time, and express the date as an Excel date value.

Wouldn't I also need to do that with a Pivot?

Thanks for your help.

SweetTasha

Answer 4

Herbert

I don't understand predominantly periods of around 4 hours and 40 minutes. Each line is for a single hour: 0:00, 0:01, 0:02, etc.

The mediafire example shows the result, right? But I would need the spreadsheet that is the source to learn how to do it.

Thanks for your help.

SweetTasha

Answer 5

Yes, you first have extract the date portion of the text value data/time.

Then the best strategy would be to convert the text date into a date data type

(Or you could do it the other way around, convert text to datetime data type, extract the date only)

There are many ways, I'm lazy so I used PowerQuery.

Then I used a PivotTable to sum the daily values.

.

I put the PQ output on the sheet, just to expose it, normally you wouldn't need to, you could load it to the Data Manager.

Here is the share link to my example: https://1drv.ms/x/s!AlV7uGd6SkRXgiMQd3Dxp-QGCyeN

Here is what it looks like.

I threw in the Chart and slicer

With the timeline slicer you can select ranges of time, ie just Dec and Jan