Split Value across date range as per defined date ?

**VICTOR5** · 12-23-2019, 02:09 AM

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

**samba_ravi** · 12-23-2019, 03:13 AM

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_ravi** · 12-23-2019, 03:19 AM

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

**Sadath31** · 12-23-2019, 03:41 AM

Hi

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

**VICTOR5** · 12-23-2019, 04:07 AM

Thank You guys.....issue resolved

Split Value across date range as per defined date ?

LinkBack

Thread Tools

Rate This Thread

Display

Split Value across date range as per defined date ?

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

Monthly split between date range

split date range into date ranges of corresponding months

How to split date range and volume into months

[SOLVED] subset of range defined by start date and date date in dedicated cells

Split data based on date range

[SOLVED] Split a date range in a row into individual rows

Set Date Range within a defined range, not to start before first date defined

Bookmarks

Bookmarks

Posting Permissions