Calculating the Difference Between Two Date fields

[nansi]

Hi All,

I'm trying to calculate the difference between two dates. Formula I'm using is

IF(MONTH(D2)=MONTH(D3),((YEAR(<wbr style="color: rgb(51, 51, 51);">D3)-YEAR(D2))*12)-12+(12-<wbr style="color: rgb(51, 51, 51);">MONTH(D2))+MONTH(D3)-1+(<wbr style="color: rgb(51, 51, 51);">EOMONTH(D2,0)-D2+1)/DAY(<wbr style="color: rgb(51, 51, 51);">EOMONTH(D2,0))+(1-(EOMONTH(D3,<wbr style="color: rgb(51, 51, 51);">0)-D3)/DAY(EOMONTH(D3,0))),((<wbr style="color: rgb(51, 51, 51);">YEAR(D3)-YEAR(D2))*12)-12+(12-<wbr style="color: rgb(51, 51, 51);">MONTH(D2))+MONTH(D3)-1+(<wbr style="color: rgb(51, 51, 51);">EOMONTH(D2,0)-D2+1)/DAY(<wbr style="color: rgb(51, 51, 51);">EOMONTH(D2,0))+(1-(EOMONTH(D3,<wbr style="color: rgb(51, 51, 51);">0)-D3)/DAY(EOMONTH(D3,0))))

Where D2= start date, D2= End date

for the dates D2= 12/31/2020, D3=8/1/2027, I should get a negative number but with the formula is get 79.06.

Is there's something I'm doing wrong here?

Thanks in advance!!

 

[jimrward]

What difference are you expecting or looking for number of days??
Why can’t you just subtract one date from the other =D3-D2

 

[nansi]

I'm trying to get number of months with decimals. Difference should be -79.06 months. If I do =(D3-D2)/31 I get -77.55

Any idea how I can do this differently?

Thanks!

 

[nansi]

Also this for a data sheet where D2-D3 is positive for some cases and negative for some.

 

[DanteAmor]

Try this


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:38.97px;" /><col style="width:37.07px;" /><col style="width:170.14px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /></colgroup><tr style="background-color:#ACC5EF; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >2</td><td > </td><td > </td><td >start date</td><td style="text-align:right; ">31/12/2020</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#ACC5EF; text-align:center; " >3</td><td > </td><td > </td><td >End date</td><td style="text-align:right; ">08/01/2027</td><td style="text-align:right; ">72.26666667</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formula</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#ACC5EF; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E3</td><td >=DATEDIF(D2,D3,"m") + DATEDIF(DATE(YEAR(D2),MONTH(D2)+DATEDIF(D2,D3,"m"),DAY(D2)),D3,"d") / 30</td></tr></table></td></tr></table>

 

[Dave Patton]

You did not indicate why the result should be negative.

Try one of the following:

Excel 2010
	D	E
2	31-Dec-20
3	1-Aug-27
4	79.03	79.03
5	-79.03	79.03
6	-79.03	-79.03
1a

Cell Formulas
Range	Formula
D4	=(D3-D2)/30.42
D5	=(D2-D3)/30.42
D6	=(D3-D2)/30.42*IF(D3>D2,-1,1)
E4	=DATEDIF(D2,D3,"m")+(D3-DATE(YEAR(D2),MONTH(D2)+DATEDIF(D2,D3,"m"),DAY(D2)))/30.42
E5	=DATEDIF(MIN(D2:D3),MAX(D2:D3),"m")+1/30.42
E6	=(DATEDIF(MIN(D2:D3),MAX(D2:D3),"m")+EOMONTH(D2,0)-D2+DAY(D3)/30.42)*(IF(D3>D2,-1,1))

 

[nansi]

THANK YOU SO MUCH! This is what I was looking for!!!

 

[DanteAmor]

Without a doubt, Dave's solutions are better than mine, but there you have one more solution.
Thanks for the feedback

 

[jimrward]

After a bit of googling the original formula as posted by nansi was found on another excel forum from a few years back without a definitive conclusion as the formula was found to be not perfect
Hopefully the above suggestions prove to be a better solution

 

[DanteAmor]

THANK YOU SO MUCH! This is what I was looking for!!!

Hi Nancy:

I found some small differences between my formula (cell E3) and Dave's, for example.
From January 1 to February 15, of the same year, the calculation is 1.5 months.


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:170.14px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >start date</td><td style="text-align:right; ">01/01/2020</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >End date</td><td style="text-align:right; ">16/02/2020</td><td style="text-align:right; ">1.5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">1.512163051</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">1.493096647</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">1.03287311</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E3</td><td >=DATEDIF(D2,D3,"m") + DATEDIF(DATE(YEAR(D2),MONTH(D2)+DATEDIF(D2,D3,"m"),DAY(D2)),D3,"d") / 30</td></tr><tr><td >E4</td><td >=(D3-D2)/30.42</td></tr><tr><td >E5</td><td >=DATEDIF(D2,D3,"m")+(D3-DATE(YEAR(D2),MONTH(D2)+DATEDIF(D2,D3,"m"),DAY(D2)))/30.42</td></tr><tr><td >E6</td><td >=DATEDIF(MIN(D2:D3),MAX(D2:D3),"m")+1/30.42</td></tr></table></td></tr></table>

-----
If you make another example, say 20 years, there are still some small differences.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:170.14px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >C</td><td >D</td><td >E</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >start date</td><td style="text-align:right; ">01/01/2020</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >End date</td><td style="text-align:right; ">16/02/2040</td><td style="text-align:right; ">241.5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">241.6502301</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">241.4930966</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">241.0328731</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >E3</td><td >=DATEDIF(D2,D3,"m") + DATEDIF(DATE(YEAR(D2),MONTH(D2)+DATEDIF(D2,D3,"m"),DAY(D2)),D3,"d") / 30</td></tr><tr><td >E4</td><td >=(D3-D2)/30.42</td></tr><tr><td >E5</td><td >=DATEDIF(D2,D3,"m")+(D3-DATE(YEAR(D2),MONTH(D2)+DATEDIF(D2,D3,"m"),DAY(D2)))/30.42</td></tr><tr><td >E6</td><td >=DATEDIF(MIN(D2:D3),MAX(D2:D3),"m")+1/30.42</td></tr></table></td></tr></table>

-----
Just check which is the closest to your calculation of months and portion of the month.