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

1. ## 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).

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

2. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

@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.?

3. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

Hi @

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

4. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

Thank you very much!

Basically, we need monthly wise report so I think it would better to split month wise?

5. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

posted twice sorry..

6. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

I really appreciate for your effort. I think that is very close to requirement.

I've some questions..
1. I don't understand Month Start, Month End Column. What does it mean?
2. How can we get accurate value for month 1 and month 2. We have Total amount so we can get Avg night Rate. I.e \$600 for 3 nights so avg is \$200

7. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

Hey Vichopalacios,

I would like to say thank you so much for help me. Your sample file helps me alot. One more thing, Need to sorting is Reference URLs month wise. Means I want to know how many guests have arrived from .co.uk in particular month. Here is updated version of your copy. Please have a look and if posisble please help me

Thank you in advance!

8. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

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

9. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

@ 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

10. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

Hi Vichopalacios,

Thank you very much! You explained very well. Really you helped me a lot. I'm a UI Designer from India. If you need any help about designing Web or Mobile App Design, feel free to let me know 24x7.. Skype: HemZone

One question, If there is a date difference between check in and checkout is more than two months then? i.e check in date: 28-sep-2015 and check out date is 22-jan-2016

11. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

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?

12. ## Re: Finding Vacancy Percentage Month Wise from Check in and Check out Date

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.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1