+ Reply to Thread
Results 1 to 2 of 2

Calculating Prorated Revenue Off of Dates with Monthly Straddles

  1. #1
    Registered User
    Join Date
    01-07-2015
    Location
    Miami, Florida
    MS-Off Ver
    2010
    Posts
    1

    Calculating Prorated Revenue Off of Dates with Monthly Straddles

    Hello,

    First post, heard a lot of great things about this forum. I have a tricky situation. I'm trying to give a prorated revenue total by month for incremental postings of revenue (ex:1/11/14-1/17/14=$76,595). Obviously the amounts like the one in my example are simple, but I'm having trouble with the straddles like: 2/22/14-3/7/14=$99,977.

    Now I know I could $99,977/13= 7,90.54 and multiply it by the respective days in each month. The issue is that I have hundreds of lines of data I need to do this for.

    The sheet looks like
    Column A: Start Date
    Column B: End Date
    Column C: Called in Revenue
    Column D: Days

    I added the following columns:
    Column E: Prior Month Straddle Days-(IF("Start of month">"Start Date","Start of month"-"Start Date",0)
    Column D: Next Month Straddle Days-(IF("End of month">"End Date", "End of month"-"End Date",0)

    I was trying to get the pro-rated cruise days from that, but I am pretty stuck.

    Can you please help?

  2. #2
    Registered User
    Join Date
    01-07-2015
    Location
    India
    MS-Off Ver
    excel 2007
    Posts
    16

    Re: Calculating Prorated Revenue Off of Dates with Monthly Straddles

    Hi Kev,

    Please can you give the query in an elaborated format in an excel spreadsheet.

    Thanks

+ 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. Replies: 1
    Last Post: 10-23-2014, 08:10 AM
  2. 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
  3. Find months between start and end dates and multiple the monthly revenue
    By Vinnie Chan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-09-2013, 05:22 PM
  4. Calculating # of monthly periods between two dates...
    By pong06 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-18-2011, 10:27 AM
  5. 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