+ Reply to Thread
Results 1 to 4 of 4

Calculating Days in Individual Months between Arrival Date and Departure Date - Table

  1. #1
    Registered User
    Join Date
    05-06-2014
    Location
    Florida
    MS-Off Ver
    Office 365
    Posts
    2

    Calculating Days in Individual Months between Arrival Date and Departure Date - Table

    Hi Everyone, Thanks for helping me out. I've been mulling on this one for a while and have it close to working, but can't seem to get things working when the year changes and on leap years.

    I'm trying to accomplish:

    Between an Arrival Date and a Departure date I want to show the number days the person has rented in each month. So, If they rented 7 nights in March it would show 7 nights in the March Column on that row. If they rented 8 nights in December and continue their stay 3 nights into January, I want it to show 8 nights in the December column and 3 nights in the January column under the same row. Keeping the data inline with the row.

    I have the attached sheet working for majority of cases, however, when the dates jump years or a leap year it no longer works. I'm trying to correct F:Q formulas.

    Thanks for the help!
    Attached Files Attached Files
    Last edited by Danimal6; 05-06-2020 at 10:41 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,260

    Re: Calculating Days in Individual Months between Arrival Date and Departure Date - Table

    can you explain why, for first row, you have 6 Jan + 1 Feb... ?

    in my head -- Jan 25th to Feb 1st would be 8 days in total, or 7 nights -- and if you're using 7 wouldn't the 1 Feb date be excluded? i.e. 7 Jan + 0 Feb

    If my above assumption is correct then, perhaps:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the -1 in the above subtracts end date, if you want this date included in the count remove it.

  3. #3
    Registered User
    Join Date
    05-06-2014
    Location
    Florida
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Calculating Days in Individual Months between Arrival Date and Departure Date - Table

    Thank you! After working through it on paper it makes sense now. I was confusing the month the night was counted in. I wish I would have asked sooner. This work perfect for what I wanted to accomplish. Much appreciated.

  4. #4
    Forum Contributor
    Join Date
    11-05-2019
    Location
    Tiki Island, TX
    MS-Off Ver
    Office 365
    Posts
    168

    Re: Calculating Days in Individual Months between Arrival Date and Departure Date - Table

    You may want to also look at the attached solution.
    Attached Files Attached Files

+ 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. [SOLVED] Sorting Arrival and departure times
    By sa1ntj0hn in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 05-22-2019, 05:35 AM
  2. [SOLVED] Formula Help: Determine days total from arrival to current date then stop adding days
    By Vicious00013 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2016, 12:45 PM
  3. [SOLVED] Calculating 2 months and 10 days from a specific date
    By pedkerr in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-07-2013, 11:01 AM
  4. Replies: 2
    Last Post: 05-30-2013, 12:45 PM
  5. [SOLVED] Calculating the number of people in building based on arrival and departure times
    By dvs in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-01-2012, 09:45 AM
  6. Replies: 2
    Last Post: 12-09-2011, 08:51 AM
  7. Replies: 3
    Last Post: 06-28-2006, 05:40 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1