Rolling Up Daily Values in a Large Spreadsheet

[damon_l]

Good Day,

I have a large spreadsheet with more than 76,000 rows.

In this spreadsheet I have 3 columns, namely Date, Time and kWh.
The kWh column gives half hourly electricity consumption values based on the Date and Time interval.

I need to roll up the date and time columns into a 24 hour period so I can get daily electricity consumption figures. What I would like is have a new column with the date such as 2018/06/01 and a 2nd corresponding column giving the corresponding total kWh for that day based on the half hourly data points in the original kWh column.

My question is what formula do I need to roll up the dates into a 24 hour period and total the kWh for that period?

Thanks
Regards

 

[damon_l]

This is what I get now..

[TABLE="width: 461"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Hourly kWh Cons[/TD]
[TD]Daily kWh [/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]00:30:00[/TD]
[TD="align: right"]33.31[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]01:00:00[/TD]
[TD="align: right"]31.808[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]01:30:00[/TD]
[TD="align: right"]32.838[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]02:00:00[/TD]
[TD="align: right"]33.383[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]02:30:00[/TD]
[TD="align: right"]32.831[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]03:00:00[/TD]
[TD="align: right"]33.604[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]03:30:00[/TD]
[TD="align: right"]33.234[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]04:00:00[/TD]
[TD="align: right"]33.072[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]04:30:00[/TD]
[TD="align: right"]33.5[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]05:00:00[/TD]
[TD="align: right"]33.043[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]05:30:00[/TD]
[TD="align: right"]55.845[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]06:00:00[/TD]
[TD="align: right"]93.672[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]06:30:00[/TD]
[TD="align: right"]91.568[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]07:00:00[/TD]
[TD="align: right"]88.741[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]07:30:00[/TD]
[TD="align: right"]96.86[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]08:00:00[/TD]
[TD="align: right"]100.542[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]08:30:00[/TD]
[TD="align: right"]106.313[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]09:00:00[/TD]
[TD="align: right"]110.68[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]09:30:00[/TD]
[TD="align: right"]109.34[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]10:00:00[/TD]
[TD="align: right"]106.127[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]10:30:00[/TD]
[TD="align: right"]102.999[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]11:00:00[/TD]
[TD="align: right"]98.701[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]11:30:00[/TD]
[TD="align: right"]99.939[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]2014/07/01[/TD]
[TD="align: right"]12:00:00[/TD]
[TD="align: right"]99.179[/TD]
[TD="align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[Repush]

the date in the first col . . is it date/time or string?
what result gives "=A2"? (general format)
[should be 41821]

NB have you tried a PivotTable?

 

[damon_l]

Date String

How do I configure pivot table to sum up the half hourly figures in according to the dates for a daily total?

 

[Repush]

what is the formula in the dayly kWh-col?

pivot:
dates in the rows field
sum of kWh Cons in the values field

 

[James006]

In order to Group Pivot Table Data ... Debra has an excellent explanation:

https://www.contextures.com/xlPivot07.html

 

[damon_l]

There's no formula there just figures.

 

[James006]

You are welcome ...