+ Reply to Thread
Results 1 to 3 of 3

Revenue allocation between two dates

  1. #1
    Registered User
    Join Date
    11-30-2019
    Location
    Tonbridge, England
    MS-Off Ver
    Office 365
    Posts
    1

    Revenue allocation between two dates

    I am trying to allocate revenue on a contract between two dates but am struggling. I'm going back and forth using IF, AND, EOMONTH but just can't get the right formula. Help please! See attached
    Attached Files Attached Files

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,964

    Re: Revenue allocation between two dates

    You might consider the YEARFRAC function which gives the fraction of the year between two dates. If you combine this with your contract size prorataed (?) for the entire year, you can do the calculation.

    Paste

    =MIN((YEARFRAC($A6,E$4) * $D6)/YEARFRAC($A6,$B6),$D6)

    into E6 and copy across
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,803

    Re: Revenue allocation between two dates

    You can use this formula in E6:

    =IF(AND($A6<=E$4,$B6>=EOMONTH(E$4,-1)+1),ROUND((MIN(E$4,$B6)-MAX(EOMONTH(E$4,-1),$A6))/($B6-$A6)*$D6,3),"")

    then copy across and down as required. I found that if I used ...ROUND( ,2) it would be 1p out in the total for the first line, whereas this formula is only 1/10th of a penny out in the total.

    Hope this helps.

    Pete

+ 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] Allocation of products based on order entry and delivery dates
    By isalmon in forum Excel General
    Replies: 6
    Last Post: 12-03-2018, 08:12 PM
  2. Replies: 10
    Last Post: 09-21-2017, 08:03 PM
  3. Replies: 1
    Last Post: 01-08-2015, 08:02 AM
  4. Milestone dates to calculate a revenue payment
    By bdlchicago in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-23-2014, 11:24 AM
  5. Calculating the revenue by month between two different dates
    By AkilaAnnamalai in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-09-2014, 04:00 AM
  6. Revenue allocation using combined/Nested IF statements
    By Irfan123 in forum Excel General
    Replies: 9
    Last Post: 07-28-2008, 09:29 AM
  7. [SOLVED] Calculating revenue per month by aggregating dates
    By Commutervet in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-17-2006, 10:20 AM

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