# Split Value across date range as per defined date ?

1. ## 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....

2. ## 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

3. ## 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. ## 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. ## Re: Split Value across date range as per defined date ?

Thank You guys.....issue resolved

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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