Finding Vacancy Percentage Month Wise from Check in and Check out Date

Hello,

I'm a newbie for excel. I'm working on one spreadsheet. I want to find out total nights(month wise) from check-in and check out date, Vacancy % (month wise), and Avg Night rate (month wise).

I've created rough format in google spreadsheet. Please check this https://docs.google.com/spreadsheets...0zw/edit#gid=0

Please make a copy of this file and then add your formulas and share a new link here.

Thank you in advance. Looking forward to get a quick reply because it's bit urgent for me. This is just request.

Let me know if you have any questions.

Thanks
Hem

@HemZone

In some lines, the month of check in is different from the check out.
Do you need to split those lines into two?
i.e:

Please Login or Register  to view this content.

Or simply calculate the hole staying to the arrival ( or departure) month.?

Hi @

Check if the attached file satisfy your request.
It's considered to split the lines with double month activity.

posted twice sorry..

Let me give a look to your new post, and I'll come back

@ HemZone

I have written some notes in the book itself,
If there is anything you want to be better explained, don't hesitate to ask.
Vicho

Second thing, I observed that if there is a date difference between months, count will night will count in month 2. For example, if you check attachment there is a #6 row Check in 31 Oct and check out is 1 Nov.. "Nights Month 1" is 0 and "Nights Month 2". I think 1 should be display for "Nights Month 1".

You got my point?

hi @HemZone
Thanks for your offer, if some day I need it, I'm counting on you.

#6 row Check in 31 Oct and check out is 1 Nov.. "Nights Month 1" is 0 and "Nights Month 2". I think 1 should be display for "Nights Month 1".

To solve this, you can just change the formula in Column F to "First day in End Month"

Please Login or Register  to view this content.

Copy this formula in cell F2 and then copy all the way down to the end of your table.
Notice that my code is in spanish, use yours....the only difference with what you have is +1 as highlighted

Regarding

i.e check in date: 28-sep-2015 and check out date is 22-jan-2016

To solve this only with formulas becomes a bit more complex, not impossible, but not nice as you would have to add in your table as many columns as the max numbers of months you can have in the future, and use the same logic for splitting them.
If it is a MUST condition, then it should be solved via a VBA Code.