# Calculation of days per month for dates beginning in one month and ending in another month

1. ## Calculation of days per month for dates beginning in one month and ending in another month

Hi,

I'm a new member and I have following situation:

I'm running a small vacation rental business. I have different rates for each month. The formula for reservations in the same month is no problem at all but I have problems with the formula for reservation beginning in one month and ending in another months. We use NIGHTLY RATES.

Sample:

Reservation 26 April - 5 May (=CELL5May-CELL26April)

Total nights: 9

Splitted as follows:
26 April - 1 May = 5 nights
1 May - 5 May = 4 nights

Rate per night in April \$50
Rate per night in May \$60

I'm looking for a formula which recognizes the nights in April and multiplicates them with the nightly rate for April and which recognizes the nights in May and multiplicates them with the nightly rate for May.

Best wishes

2. ## Re: Calculation of days per month for dates beginning in one month and ending in another m

in C1

=MIN(B1-A1+1,EOMONTH(A1,0)-A1) will calculate days in start month whether or not date in B1 is in next month

in D1

=MAX(B1-EOMONTH(A1,0),0) will give days in second month or 0 if finish is in same month as start

Using A1 and B1 dates you can lookup rates in a table and multiple by C1 and D1 respectively.

=VLOOKUP(A1,F1:G12,2)*C1 + VLOOKUP(B1,F1:G12,2)*D1

Table below in columns F & G

F G
Date ......... Rate
01/01/2015 10
01/02/2015 20
01/03/2015 30
01/04/2015 40
01/05/2015 50
01/06/2015 60
01/07/2015 70
01/08/2015 80
01/09/2015 90
01/10/2015 100
01/11/2015 110
01/12/2015 120

3. ## Re: Calculation of days per month for dates beginning in one month and ending in another m

@John Topley

Thanks for the fast reply. But unfortunately I get error messages when I use these formulas. Screen shots attached.
Capture1.PNG
Capture2.PNG
Capture3.PNG
What do I do wrong?
Thanks again

4. ## Re: Calculation of days per month for dates beginning in one month and ending in another m

Screen shots are no good: please post a file with your data.

5. ## Re: Calculation of days per month for dates beginning in one month and ending in another m

See attached.

6. ## Re: Calculation of days per month for dates beginning in one month and ending in another m

Hi, Thanks again. Very close but our business is a bit different. We charge per nights. I have made my notes on the attached sheet.

7. ## Re: Calculation of days per month for dates beginning in one month and ending in another m

See attached.

8. ## Re: Calculation of days per month for dates beginning in one month and ending in another m

That's it. Thx a lot.

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