+ Reply to Thread
Results 1 to 5 of 5

Split the equal amount acoss months based on start and end date

  1. #1
    Registered User
    Join Date
    01-31-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    6

    Split the equal amount acoss months based on start and end date

    Hello Everyone,

    I have contarct for amount $5000 with a start date of 1/1/2014 - 3/31/2014 (3 months). I would like to equally split my amount based on my number of months between 1/1/2014 and 3/31/2014 ie $1666.66/per month.

    Any thoughts recommendation ?

    Thank you everyone.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Split the equal amount acoss months based on start and end date

    Hi and welcome to the forum

    Assuming your data is laid out like this...

    A
    B
    C
    D
    1
    5000
    1/1/2014
    3/31/2014
    1666.667


    D1=A1/(MONTH(C1)-MONTH(B1)+1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Split the equal amount acoss months based on start and end date

    This version will account for dates that may span multiple years.

    A2 = 5000
    B2 = start date
    C2 = end date


    =A2/(DATEDIF(B2-DAY(B2)+1,C2,"m")+1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    01-31-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Split the equal amount acoss months based on start and end date

    Hello Everyone,

    Thank you for your response, but I should see my amounts only for month Jan, Feb and March given my start and end date is 1/1/2014 - 3/31/2014. I have attached a sample of data. Can you suggest any workarounds ?

    Thanks

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Split the equal amount acoss months based on start and end date

    Will the date range always be within the same year?

    Since there are only 12 months as the column headers I assume that will be the case?

+ 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. Dynamically Filling in Allocation in Months based only on Start Date and End Date
    By Computermoss in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-07-2013, 01:16 PM
  2. Replies: 3
    Last Post: 10-23-2012, 09:04 PM
  3. [SOLVED] Calculate date based on number of weeks or months after start date
    By wotsup in forum Excel General
    Replies: 3
    Last Post: 08-17-2012, 06:00 AM
  4. Replies: 2
    Last Post: 06-14-2006, 11:00 AM
  5. Code for auto filling in Excel based on number of months, & start date
    By Diane Sulton in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-28-2006, 09:20 PM

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