+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    11-30-2015
    Location
    Turkey
    MS-Off Ver
    Office 365 Home
    Posts
    4

    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.

    Thanks in advance.
    Best wishes

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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
    Last edited by JohnTopley; 12-07-2015 at 10:34 AM.

  3. #3
    Registered User
    Join Date
    11-30-2015
    Location
    Turkey
    MS-Off Ver
    Office 365 Home
    Posts
    4

    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. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

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

    See attached.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    11-30-2015
    Location
    Turkey
    MS-Off Ver
    Office 365 Home
    Posts
    4

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

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

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

    See attached.
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    11-30-2015
    Location
    Turkey
    MS-Off Ver
    Office 365 Home
    Posts
    4

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

    That's it. Thx a lot.

+ 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] How can I fill a column with dates just knowing beginning and ending month ?
    By dschmitt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-07-2015, 03:23 AM
  2. [SOLVED] Determine work days in current month or next month based on day of the month
    By sbrnard in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-29-2014, 05:14 PM
  3. find time left in month.days unit between two dates(in YEAR.MONTH.DAY)
    By xcfeng95 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-21-2014, 12:55 PM
  4. Date Formula for days of the month, new dates starting on the 2nd of the month.
    By Kenn Jerger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2013, 01:31 AM
  5. Formula for average duration for each month based on the ending month
    By bobby769 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-21-2013, 11:18 AM
  6. [SOLVED] Help creating a date range with beginning and ending days of the month.
    By charlesbb88 in forum Excel General
    Replies: 6
    Last Post: 04-27-2012, 09:28 PM
  7. Dates - Several Days In a month to month only
    By Andy_Pimp in forum Excel General
    Replies: 1
    Last Post: 02-28-2006, 07:15 AM

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