+ Reply to Thread
Results 1 to 5 of 5

Split Value across date range as per defined date ?

  1. #1
    Forum Contributor
    Join Date
    06-03-2010
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    Excel 2013
    Posts
    122

    Split Value across date range as per defined date ?

    Hi to all experts,

    I am looking for a formula which split the value throughout date range as per start and finish milestone dates:

    example : Item A has start and finish date and total duration for it is 65 days, total manhours/value for it is 1,000. so I want monthly values to be allocated in January, February & March (taking into consideration range from G5 to AP5) as I reflected data manually.

    example sheet is attached for reference

    thanks....
    Attached Files Attached Files

  2. #2
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,636

    Re: Split Value across date range as per defined date ?

    g9=IFERROR(1/(1/(IF(AND($C9<=EOMONTH(G$5,0),$D9>EOMONTH(G$5,-1)),(MIN($D9,EOMONTH(G$5,0)+1)-MAX(EOMONTH(G$5,-1)+1,$C9)),0)*$F9/$E9)),"")
    Please Login or Register  to view this content.
    Try this, copy and paste across
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.

  3. #3
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2013
    Posts
    8,636

    Re: Split Value across date range as per defined date ?

    or can try
    =IFERROR(1/(1/(MAX(0,MIN($D9,EOMONTH(G$5,0)+1)-MAX(EOMONTH(G$5,-1)+1,$C9))*$F9/$E9)),"")

  4. #4
    Valued Forum Contributor Sadath31's Avatar
    Join Date
    03-02-2011
    Location
    Dammam, Saudi Arabia
    MS-Off Ver
    Excel 2019
    Posts
    447

    Re: Split Value across date range as per defined date ?

    Hi

    Formula in G9 =SUMPRODUCT((MONTH(ROW(INDIRECT($C9&":"&$D9-1)))=MONTH(G$5))*1)/$E9*$F9

  5. #5
    Forum Contributor
    Join Date
    06-03-2010
    Location
    Jeddah, Saudi Arabia
    MS-Off Ver
    Excel 2013
    Posts
    122

    Re: Split Value across date range as per defined date ?

    Thank You guys.....issue resolved

+ 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. Monthly split between date range
    By Jaydj1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-11-2019, 02:13 PM
  2. split date range into date ranges of corresponding months
    By milosvica in forum Excel General
    Replies: 1
    Last Post: 10-28-2017, 02:40 AM
  3. How to split date range and volume into months
    By okok15266 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2016, 11:13 AM
  4. [SOLVED] subset of range defined by start date and date date in dedicated cells
    By Pragmaticite in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-23-2015, 05:30 PM
  5. Split data based on date range
    By prabhubox@yahoo.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-03-2014, 06:30 AM
  6. [SOLVED] Split a date range in a row into individual rows
    By Vicko in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-04-2012, 05:51 AM
  7. Set Date Range within a defined range, not to start before first date defined
    By Chicago_girl1 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-06-2012, 01:31 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