Forum Discussion
Summing consumption for night hours only
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.
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
RonRichmond Just using MONTH(G2) would lump all entries of months with the same number in different years. It would also have done that with the week-numbers, by the way. Use, for instance, this formula to combine year and month into one text string.
=YEAR(G2)&"-"&MONTH(G2)Then in the SUMIF formula, point at texts like "2018-11" rather than the week numbers 44 and 45.
And, please have a look at SergeiBaklan 's PQ solution. Very worthwhile, but perhaps not so straight-forward if this is the first time you look at it.
