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

**Fahrettin** · 12-07-2015, 09:45 AM

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.

Thanks in advance.
Best wishes

**JohnTopley** · 12-07-2015, 10:05 AM

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

**Fahrettin** · 12-07-2015, 12:38 PM

@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

**JohnTopley** · 12-07-2015, 12:59 PM

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

**JohnTopley** · 12-07-2015, 01:48 PM

See attached.

**Fahrettin** · 12-07-2015, 02:22 PM

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.

**JohnTopley** · 12-07-2015, 03:13 PM

See attached.

**Fahrettin** · 12-07-2015, 04:52 PM

That's it. Thx a lot.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How can I fill a column with dates just knowing beginning and ending month ?

[SOLVED] Determine work days in current month or next month based on day of the month

find time left in month.days unit between two dates(in YEAR.MONTH.DAY)

Date Formula for days of the month, new dates starting on the 2nd of the month.

Formula for average duration for each month based on the ending month

[SOLVED] Help creating a date range with beginning and ending days of the month.

Dates - Several Days In a month to month only

Bookmarks

Bookmarks

Posting Permissions