Adrian has attended:
Excel VBA Introduction course
Excel VBA Intermediate course
Excel Intermediate course
Monthly Totals based on Date Range
Hi
I want to summarize monthly totals throughout the year(s) based on 2 dates Start (Column E) & End(Column F) from those hourly figures in column D. My data looks like the following:
Column D Column E Column F
73000 24/11/2015 06:00 25/11/2015 06:00
70000 25/11/2015 06:00 28/11/2015 06:00
112000 26/11/2015 06:00 27/11/2015 06:00
90000 27/11/2015 06:00 28/11/2015 06:00
234000 28/11/2015 06:00 01/12/2015 06:00
760000 01/12/2015 06:00 02/12/2015 06:00
750000 02/12/2015 06:00 03/12/2015 06:00
752000 03/12/2015 06:00 04/12/2015 06:00
166000 04/12/2015 06:00 05/12/2015 06:00
100000 01/01/2016 06:00 01/01/2017 06:00
460000 05/12/2015 06:00 06/12/2015 06:00
470000 06/12/2015 06:00 07/12/2015 06:00
The majority of dates are just 1 day, but some may be for 3 days, and you will see 1 date is for the following year. I don't want to sort the data because the figures are in order of when bookings were made (another column not shown here).
Example is, Based off another cell that contains the date August, add all those figures in Column D/1000*24 (essentially converting hourly kwh to daily mwh) that were in August (based off the dates)
Any help would be much appreciated, many thanks.
Kind regards
Adrian
RE: Monthly Totals based on Date Range
Hi Adrian
One way to summarise your data is by a PvitTable.
Then group the Start date by month and year
After creating the pivot table
Row Labels Start Date
Values - Sum of mwh
Then right click on a start date, click Group on Month and Year)
The pivot summary then looks like this:
Row Labels Sum of mwh
2015
Nov 579,000
Dec 3,358,000
2016
Jan 100,000
Grand Total 4,037,000
The only issue is for dates overlapping two months, for example 28/11/15 to 01/12/15 6:00
Could data overlapping 2 months be broken down into two separate entries?
Hope that helps for now.
Doug
Best STL
RE: Monthly Totals based on Date Range
Hi Doug
Thanks for your quick response, but unfortunately the data couldn't be broken down in to separate months. These entries can span for the entire year as per the example.
Kind regards
Adrian
RE: Monthly Totals based on Date Range
Hi Doug
Another approach could be to enter 2 dates ie 1st of month and last of month in a couple of cells and then calculate any figures in Column D where Column E and F fall within those dates - Sumifs I think
RE: Monthly Totals based on Date Range
Hi Adrian
I hope this helps. I found a way to estimate monthly totals. For power values spanning over more than one month the average per month is used.
D E F G H I J
mwh StartDate EndDate Coverage(mths) mwhThatMonth 1/11/15 1/12/15 ...
73000 24/11/2015 25/11/2015 1 73,000 73,000
234000 28/11/2015 01/12/2015 2 - 117,000 166,000
100000 01/01/2016 01/01/2017 12 - - 8,333 8,333 8,333 8,333 8,333 etc
Formula in G2 (Calculates coverage in months)
=IF(AND(MONTH(E2)=MONTH(F2),YEAR(E2)=YEAR(F2)),1,IF(AND(MONTH(F2)>MONTH(E2),YEAR(E2)=YEAR(F2)),MONTH(F2)-MONTH(E2)+1,(YEAR(F2)-YEAR(E2))*12))
Formula in H2 (calcualtes mwh for a month or average per month)
=IF(G2>1,D2/G2,D2)
Formula in I2
=IF(AND($E2>=I$1,$E2<J$1),$H2,IF(AND($F2>=I$1,$E2<I$1),$H2,""))
Se attached. Conditional formatting is used to highlight figures that are estimates based on the monthly average.
Regards
Doug
Best STL
Attached files...
mwh by month.xlsxRE: Monthly Totals based on Date Range
Hi Doug
Thank you very much for your help and advice, its really appreciated. This solution really seems to help!!
Kind regards
Adrian
RE: Monthly Totals based on Date Range
Glad that worked.
Sound like you received the file ok.
Best wishes
Doug
