Carry over values from one month to the next

[AmrithS]

Hi, this is for a hotel application. My input data is a list of bookings which has a starting date (First night) and corresponding number of 'Total nights' stayed. I'd like to calculate the total number of days stayed in each month, but the problem occurs when a booking starts in one month and carries over into the next.

My first thought was to add a column that calculates the number of days in the 'Starting month' and then have another column which is the balance of days which fall into the carryover month.

Is it possible to make a pivot table from this that sums up all the nights for each month? (OR some other method that will have the same result).

Thx

[mikeTRON]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[AmrithS]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need.

Hi Mike, done (pls see original post). Let me know if there's any further clarification needed.

[oeldere]

d17 =if($C17<=eomonth($A17,0),$C17-$A17,eomonth($A17,0)+1-$A17)

e17 =B17-D17

f17 =MONTH(A17)

g17 =MONTH(C17)

l17 =SUMIF($F$17:$F$20,L$16,$D$17:$D$20)+SUMIF($G$17:$G$20,L$16,$E$17:$E$20) and drag to the right.

See the attached file.

[AmrithS]

Thanks oeldere, your EOMONTH code really cleans up some of my interim steps, and will help since my source data is quite large


Though I should have mentioned that I need the final result to be a pivot table. I simplified the input data table, but there's a lot of other fields that I need to analyze for, which can't be done with the SUMIF statement.



What I'd like to do is make a pivot table, from two data sources for the same data type (i.e. month vs nights stayed), but they're both in different locations on the same table. Not sure if my problem definition makes sense

[oeldere]

in that cade you should split the data into the 2 diffeant months on a diffeant row, then you will be able to make a pivot table of it.

0/06/2016 - 31/06/2016
01/07/2016 - 15/07/2016

[AmrithS]

The data is an export from an external source, so I can't control how it's stored/displayed.

[oeldere]

I22 =IF(INDIRECT("A"&(ROW()/2)+6)="","",INDIRECT("A"&(ROW()/2)+6))

I23 =IFERROR(VLOOKUP(I22,Tabel1,3;0),"")

The other cells are with VLookup.

and both (together) drag down.

After that a pivot table.

See the attached file.

[AmrithS]

Hi Oeldere, we have multiple check-ins per any given day (I just simplified the input table), hence there will be multiple line items for the same date, and VLOOKUP won't function.


Is there any way to achieve this via a pivot table calculated field?

[oeldere]

show the amend file, with all options.

please add the expected result in your file.

[AmrithS]

Hi Oeldere, please see attached.

[oeldere]

You sheet Result Ideal Layout is made (by you) with a pivot table.

You want the result made by a pivot table, and already made one.

I don't understand what the question is, you want me to solve.

[AmrithS]

The report that I made only takes into account the start month data.

So the data is incomplete because the "End month numbers" (i.e. bookings which started in the previous month and were carried over) are not included in the data set. It's between 3 & 25% of the monthly occupancy, so it's a pretty significant chunk of data.