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

**Danimal6** · 05-06-2020, 10:38 AM

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!

**XLent** · 05-06-2020, 11:11 AM

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:

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.

**Danimal6** · 05-06-2020, 11:45 AM

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.

**DavidBowman** · 05-06-2020, 12:29 PM

You may want to also look at the attached solution.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Sorting Arrival and departure times

[SOLVED] Formula Help: Determine days total from arrival to current date then stop adding days

[SOLVED] Calculating 2 months and 10 days from a specific date

[SOLVED] Populate a daily calendar using the dates between Date of Arrival and Date of Departure

[SOLVED] Calculating the number of people in building based on arrival and departure times

Using Date/Time field set to Medium Date to Query for individual months

FORMULA FOR CALCULATING YEARS, DAYS, & MONTHS BETWEEN SEVERAL DATE

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions