+ Reply to Thread
Results 1 to 12 of 12

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

  1. #1
    Registered User
    Join Date
    10-21-2015
    Location
    India
    MS-Off Ver
    2011
    Posts
    7

    Thumbs up 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
    Last edited by HemZone; 10-21-2015 at 01:44 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    453

    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.?
    Barriers are there for those who don't want to dream

  3. #3
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    453

    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.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-21-2015
    Location
    India
    MS-Off Ver
    2011
    Posts
    7

    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. #5
    Registered User
    Join Date
    10-21-2015
    Location
    India
    MS-Off Ver
    2011
    Posts
    7

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

    posted twice sorry..
    Last edited by HemZone; 10-26-2015 at 07:32 AM. Reason: posted twice sorry..

  6. #6
    Registered User
    Join Date
    10-21-2015
    Location
    India
    MS-Off Ver
    2011
    Posts
    7

    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. #7
    Registered User
    Join Date
    10-21-2015
    Location
    India
    MS-Off Ver
    2011
    Posts
    7

    Thumbs up 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!
    Attached Files Attached Files

  8. #8
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    453

    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. #9
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    453

    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
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    10-21-2015
    Location
    India
    MS-Off Ver
    2011
    Posts
    7

    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. #11
    Registered User
    Join Date
    10-21-2015
    Location
    India
    MS-Off Ver
    2011
    Posts
    7

    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?
    Attached Images Attached Images
    Last edited by HemZone; 10-29-2015 at 03:52 AM.

  12. #12
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    453

    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.
    Last edited by vichopalacios; 10-29-2015 at 10:54 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 11
    Last Post: 06-06-2014, 03:34 PM
  2. how to hide formula in formula box, view lookup result in formula box?
    By vengatvj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2013, 04:06 PM

Bookmarks

Posting Permissions

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07322 seconds
  • Memory Usage 9,162KB
  • Queries Executed 16 (?)
More Information
Template Usage (36):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_code
  • (2)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (12)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (4)postbit_attachment
  • (12)postbit_legacy
  • (12)postbit_onlinestatus
  • (12)postbit_wrapper
  • (4)showthread_bookmarksite
  • (2)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1