Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

monthly totals based date

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Monthly Totals based on Date Range

Monthly Totals based on Date Range

ResolvedVersion 2010

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

RE: 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

Edited on Tue 22 Dec 2015, 16:15

RE: Monthly Totals based on Date Range

Glad that worked.
Sound like you received the file ok.

Best wishes
Doug

Tue 29 Dec 2015: Automatically marked as resolved.

Excel tip:

Creating custom lists

In Excel if you type in January in a cell, you can then copy this cell to replicate Febraury, MArch, April etc.

This list has come from Tools- options and Custom lists.

Therefore to save time and create your own list you can click on New (in Tools and custom list tab) and type out the lsit that you want copied quickly.

All you have to do is then type in the 1st word and you will be able to copy the rest of the list quickly.

View all Excel hints and tips

Connect with us:

0207 987 3777

Call for assistance

Request Callback

We will call you back

Server loaded in 0.11 secs.