Summing Values Based on Date/Month

Hi all,

I have the following information: START DATE (Column A), FINISH DATE (Column B), QUANTITY (Column C). And a number of rows containing data under these column headings.
It is basically for accommodation so Start/Finish are Check-in/Check-out dates and Quantity is number of nights accommodation is required for (ie Finish less Start).

I need to produce a report that tells me how many nights of accommodation I require per month.

I am trying to come up with a solution for how to capture quantities for bookings where check-in and check-out are in 2 separate months. For example:
START = 21-Jan-16
FINISH = 10-Feb-16
QUANTITY = 20

So for this booking I require 11 nights of accommodation in January and 9 nights of accommodation in February. I need to reflect this in my report - which is a table with the Months as the header row and the figure for each month on the row below.

I seem to be hitting brick walls with each idea I've tried to make this work. Appreciate any help you guys can provide.

I can add additional rows or columns as needed. I received a table with START, FINISH & QUANTITY that I can modify and the only restriction on the report is that I need to produce a total for each month of nights accommodation required.

Cheers.

Hi, welcome to the forum

What you need to understand about dates and times in excel is...

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Thu Apr 2016) is actually 42488

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

question 1...will the dates spread over more than 2 months?

To get the days from a given date to the end of that month (or any number of months away), you would use EOMONTH()

	K	L
35	1/1/2019	1/31/2019
36	1/5/2016	1/31/2016
37	2/20/2016	2/29/2016
38	5/19/2016	5/31/2016

L35=EOMONTH(K35,0)
copied down.

Play around with that and see if that gets you started?
If you still have a problem, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

Hi @TARR01,

I guess you can use SUMIFS.

Can you explain how you are calculating 9, 23, 25, 59 for Dec, Jan, Feb, Mar respectively?

Regards,

Hi Khalid,

I have tried Gus80's solution and it does work but if there is a simpler or tidier way of doing it then I'll give that a go.

For the example I have manually calculated those values. I have manually looked at each line and worked out quantities to allocate to each month.

For example, row 13 has a Start of 27-Feb-16 and a Finish of 15-Mar-16.
So the values to go into the report from this line are 3 for February (27, 28 & 29 Feb) and 14 for March (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 & 14 Mar).
I have done this for each line in the source data and added the totals for each month together to get the monthly totals in columns H to M.

See below I have quickly added my calculations to the end column of the source data.

Traveller Name Check In Check Out Nights
Hammer, Amy 10-Dec-15 19-Dec-15 9 Dec=9
Hammer, Amy 05-Mar-16 27-Mar-16 22 Mar=22
Green, Bob 21-Jan-16 10-Feb-16 20 Jan=11, Feb=9
Boleyn, Chris 04-Jan-16 07-Jan-16 3 Jan=3
Boleyn, Chris 29-Jan-16 02-Feb-16 4 Jan=3, Feb=1
Boleyn, Chris 01-Mar-16 07-Mar-16 6 Mar=6
Irons, Dan 20-Jan-16 26-Jan-16 6 Jan=6
Daggers, Emma 28-Feb-16 07-Mar-16 8 Feb=2, Mar=6
Upton, Frank 01-Feb-16 11-Feb-16 10 Feb=10
Upton, Frank 27-Feb-16 15-Mar-16 17 Feb=3, Mar=14
West, Gordon 09-Mar-16 20-Mar-16 11 Mar=11



Thanks.

I agree that there must be a simpler way to do that, but I am trying to find a different wall to bang my head on right now

Hi @TARR01,
Thanks for the explanation.

I have used few helper columns, see the attached, It may look crazy but it works.

Blessing