Date Formula Problem

[gheyman]

I am trying to determine how much of a value goes in each Year based on the dates.

(If my date range was 12/1/2018 and 1/1/2019 and a value of 48, my results should be 24 in 2018 and 24 in 2019)
(If my Date range was 11/1/2018 and 1/1/2019 and my value was 30, my results should be 20 in 2018 and 10 in 2019)

Here is my data:

In Cell A2 I have "48" in B2 I have 4/30/2018 and in C2 I have 5/5/2018 - in D1 I have 2018, E1 2019, F1 2020..

So I am trying to have a formula in D2 through F2 that parses the value of 48 into the years. So in this example the dates are all in 2018 so 48 should be in D2

My Formula: =IFERROR(((DATEDIF(MAX($B2,DATE(D$1,1,1)),MIN($C2,DATE(D$1,12,31)),"ym")+1)*($A2/(((IFERROR((((YEAR($C2)-YEAR($B2))*12+MONTH($C2)-MONTH($B2))),"")))+1))),0)

My formula is only putting 24 in 2018. The Answer should be 48

Anyone see my error???

 

[NeonRedSharpie]

Re: Date Fromula Problem

Can you explain the business logic for the formula? It's is just parsing it based on months remaining in the year/passed in the year?

Example 1: one month in 2018 and one month in 2019 so 50/50 split
Example 2: two months in 2018 and one month in 2019 so 66/33 split

 

[gheyman]

Re: Date Fromula Problem

That's exactly right

 

[NeonRedSharpie]

Re: Date Fromula Problem

It's because your datedif() function returns #NUM for 2019 and 2020. Datedif() will throw a #NUM error if the start date is greater than the end date. So essentially you're only assigning one value (0+1) to D24 and then (#NUM + 1) to the remainder. Give me a second to think through a new formula for you.

 

[NeonRedSharpie]

Re: Date Fromula Problem

<year($c$2)),12,0),if(and(d$1=year($b$2),d$1=year($c$2)=false),datedif($b$2,date(d$1,12,31),"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)=false),datedif(date(d$1,1,1),$c$2,"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)),(month($c$2)-month($b$2)),0),0) ((month($c$2)-month($b$2))="" +((year($c$2)-year($b$2))*12)="" -="" 1),0)*$a$2[="" code]
<year($c$2)),12,0),if(and(d$1=year($b$2),d$1=year($c$2)=false),datedif($b$2,date(d$1,12,31),"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)=false),datedif(date(d$1,1,1),$c$2,"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)),(month($c$2)-month($b$2)),0),0) ((month($c$2)-month($b$2))="" +((year($c$2)-year($b$2))*12)="" -="" 1),0)*$a$2[="" code]
Give that a go. It should also work for when the month range spans multiple years.

=IFERROR(MAX(IF(AND(D$1>YEAR($B$2),D$1 < YEAR($C$2)),12,0),IF(AND(D$1=YEAR($B$2),D$1=YEAR($C$2)=FALSE),DATEDIF($B$2,DATE(D$1,12,31),"YM"),0),IF(AND(D$1=YEAR($C$2),D$1=YEAR($B$2)=FALSE),DATEDIF(DATE(D$1,1,1),$C$2,"YM"),0),IF(AND(D$1=YEAR($C$2),D$1=YEAR($B$2)),(MONTH($C$2)-MONTH($B$2)),0),0)/((MONTH($C$2)-MONTH($B$2)) +((YEAR($C$2)-YEAR($B$2))*12) - 1),0)*$A$2<year($c$2)),12,0),if(and(d$1=year($b$2),d$1=year($c$2)=false),datedif($b$2,date(d$1,12,31),"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)=false),datedif(date(d$1,1,1),$c$2,"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)),(month($c$2)-month($b$2)),0),0) ((month($c$2)-month($b$2))="" +((year($c$2)-year($b$2))*12)="" -="" 1),0)*$a$2<="" year($c$2)),12,0),if(and(d$1="year($b$2),d$1=year($c$2)=false),datedif($b$2,date(d$1,12,31),"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)=false),datedif(date(d$1,1,1),$c$2,"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)),(month($c$2)-month($b$2)),0),0)"></year($c$2)),12,0),if(and(d$1=year($b$2),d$1=year($c$2)=false),datedif($b$2,date(d$1,12,31),"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)=false),datedif(date(d$1,1,1),$c$2,"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)),(month($c$2)-month($b$2)),0),0)></year($c$2)),12,0),if(and(d$1=year($b$2),d$1=year($c$2)=false),datedif($b$2,date(d$1,12,31),"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)=false),datedif(date(d$1,1,1),$c$2,"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)),(month($c$2)-month($b$2)),0),0)></year($c$2)),12,0),if(and(d$1=year($b$2),d$1=year($c$2)=false),datedif($b$2,date(d$1,12,31),"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)=false),datedif(date(d$1,1,1),$c$2,"ym"),0),if(and(d$1=year($c$2),d$1=year($b$2)),(month($c$2)-month($b$2)),0),0)>

 

[gheyman]

I am getting a zero value from the formula.

 

[NeonRedSharpie]

=((IFERROR(MAX(IF(AND(D$1=YEAR($C$2),D$1=YEAR($B$2)),(MONTH($C$2)-MONTH($B$2)),0),IF(AND(D$1>YEAR($B$2),D$1<
YEAR($C$2)),12,0),IF(AND(D$1=YEAR($B$2),D$1=YEAR($C$2)=FALSE),DATEDIF($B$2,DATE(D$1,12,31),"YM"),0),IF(AND(D$1=YEAR($C$2),D$1=YEAR($B$2)=FALSE),DATEDIF(DATE(D$1,1,1),$C$2,"YM"),0),IF(AND(D$1=YEAR($C$2),D$1=YEAR($B$2)),(MONTH($C$2)-MONTH($B$2)),0),0),0)) + IF(AND(MONTH($C$2)=1,D$1=YEAR($C$2)),1,IF(AND(MONTH($B$2)=12,D$1=YEAR($B$2)),1,0))+ IF(AND(MONTH($C$2)=12,D$1=YEAR($C$2)),1,IF(AND(MONTH($B$2)=1,D$1=YEAR($B$2)),1,0)))/(((YEAR($C$2)-YEAR($B$2))*12)+(MONTH($C$2)-MONTH($B$2))+1)*($A$2)

One more go at it, with more error handling.

 

[gheyman]

I appreciate the time and effort. Did yours give you the right answer? I am not getting the correct answers.