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..

Date	Time	Hourly kWh Cons	Daily kWh
2014/07/01	00:30:00	33.31	0
2014/07/01	01:00:00	31.808	0
2014/07/01	01:30:00	32.838	0
2014/07/01	02:00:00	33.383	0
2014/07/01	02:30:00	32.831	0
2014/07/01	03:00:00	33.604	0
2014/07/01	03:30:00	33.234	0
2014/07/01	04:00:00	33.072	0
2014/07/01	04:30:00	33.5	0
2014/07/01	05:00:00	33.043	0
2014/07/01	05:30:00	55.845	0
2014/07/01	06:00:00	93.672	0
2014/07/01	06:30:00	91.568	0
2014/07/01	07:00:00	88.741	0
2014/07/01	07:30:00	96.86	0
2014/07/01	08:00:00	100.542	0
2014/07/01	08:30:00	106.313	0
2014/07/01	09:00:00	110.68	0
2014/07/01	09:30:00	109.34	0
2014/07/01	10:00:00	106.127	0
2014/07/01	10:30:00	102.999	0
2014/07/01	11:00:00	98.701	0
2014/07/01	11:30:00	99.939	0
2014/07/01	12:00:00	99.179	0

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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 ...