Forum Discussion
Summing consumption for night hours only
Hi, I have daily solar production/consumption report in 15 minute increments. I would like to subtract the daytime consumption figures so that I can work out what the nightly consumption is.
The Date/Time in column A is shown as 2018-11-01 00:30:00, 2018-11-01 00:45:00, etc
Column B shows Solar production in Wh, and Column C shows Energy Consumption in Wh.
I can easily get a weekly total of Energy Consumption, but I would like to break that into daytime consumption vs nighttime consumption.
So how do I total the energy consumption figures for the hours between 06:00:00 and 17:45:00 on a weekly basis so I can subtract this to work out by nightly consumption.
Many thanks
Ron Richmond
RonRichmond Please see the attached workbook. I left your data as it was but added some columns towards the right to demonstrate a solution. First you need to separate the Date, Time and Time zone and make sure that the dates are real dates (not texts looking like dates) and that the time entries are formatted as Time. I used "Text-to-columns" in the Data ribbon. Then you can use the dates to calculate week-numbers and the times to see which fall in the day bracket. Use SUMIF to summarise by week and day or night as shown from row 675 and down.
Now, if this something that you want to update very frequently, you may want to look into a more automated solution using Get&Transform (a.k.a. Power Query, found also on the Data ribbon, Windows365). You record all the steps as explained above and make a summary once and save it. You can then use it over and over again. Not too difficult but you have to learn a few more tricks and it requires some getting used to, if you have never used it before.
5 Replies
RonRichmond Please see the attached workbook. I left your data as it was but added some columns towards the right to demonstrate a solution. First you need to separate the Date, Time and Time zone and make sure that the dates are real dates (not texts looking like dates) and that the time entries are formatted as Time. I used "Text-to-columns" in the Data ribbon. Then you can use the dates to calculate week-numbers and the times to see which fall in the day bracket. Use SUMIF to summarise by week and day or night as shown from row 675 and down.
Now, if this something that you want to update very frequently, you may want to look into a more automated solution using Get&Transform (a.k.a. Power Query, found also on the Data ribbon, Windows365). You record all the steps as explained above and make a summary once and save it. You can then use it over and over again. Not too difficult but you have to learn a few more tricks and it requires some getting used to, if you have never used it before.
- Thanks Riny_van_Eekelen. I will test this out as I have at least 18 months of data to summarize. Will let you know if I have any problems. Cheers, Ron
Riny_van_EekelenWOW! I've just produced the same figures as you had suggested. Thank you for responding so quickly with the recommended solution.
Since I have many months of data, I presume I can replace the Week function with the Month function in my calculations.
Again many thanks.
Cheers,
Ron
