Datedif function help

[rex759]

Hello,

I’m having trouble with the Datedif function calculating the correct number of days when evaluating 1/31/11 to 1/30/12. It should read 11 months and 30 days instead of 11 months and 143 days.

Any help is appreciated.

F2=IF(D2="none","First - "&DATEDIF(E2,A2,"m")&" Months "&DATEDIF(E2,A2,"md")&" Days",DATEDIF(D2,A2,"m")&" Months "&DATEDIF(D2,A2,"md")&" Day(s) ")

Book1.xlsx
	A	B	C	D	E	F
1	2012			2011	OpenDate	TimeSpan
2	1/30/2012			1/31/2011		11Months143Day(s)
3	1/31/2012			2/1/2011		11Months30Day(s)
4	3/3/2012			None	5/7/2011	First-9Months25Days
Sheet2

 

[Dave Patton]

=DATEDIF(D2,A2,"ym")&" Months "&DATEDIF(D2,A2,"md")&" Day(s) "

yields 11 Months 30 Day(s).

Try
- checking your formula
- if you are using Excel 2007 ensure you have installed all the updates
- search for threads dealing with this subject on this forum

 

[MrKowz]

=DATEDIF(D2,A2,"ym")&" Months "&DATEDIF(D2,A2,"md")&" Day(s) "

yields 11 Months 30 Day(s).

Try
- checking your formula
- if you are using Excel 2007 ensure you have installed all the updates
- search for threads dealing with this subject on this forum

Strangely enough, it is bugging on my end as well. I'm interested to see what might be the cause of this.

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">1/30/2012</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right">1/31/2011</TD><TD>11 Months 143 Day(s) </TD></TR></TBODY></TABLE><TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH>Sheet1</TH></TR></TD></THEAD><TBODY></TBODY></TABLE>

<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>E2</TH><TD style="TEXT-ALIGN: left">=DATEDIF(D2,A2,"ym")&" Months "&DATEDIF(D2,A2,"md")&" Day(s) "</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

 

[texasalynn]

it works for me

Excel Workbook
	A	B	C	D	E	F
1	2012			2011	Open Date	Time Span
2	1/30/2012			1/31/2011		11 Months 30 Day(s)
3	1/31/2012			2/1/2011		11 Months 30 Day(s)
4	3/3/2012			None	5/7/2011	First - 9 Months 25 Days
Sheet1

Excel 2003

Cell Formulas
Range	Formula
F2	=IF(D2="none","First - "&DATEDIF(E2,A2,"m")&" Months "&DATEDIF(E2,A2,"md")&" Days",DATEDIF(D2,A2,"m")&" Months "&DATEDIF(D2,A2,"md")&" Day(s) ")
F3	=IF(D3="none","First - "&DATEDIF(E3,A3,"m")&" Months "&DATEDIF(E3,A3,"md")&" Days",DATEDIF(D3,A3,"m")&" Months "&DATEDIF(D3,A3,"md")&" Day(s) ")
F4	=IF(D4="none","First - "&DATEDIF(E4,A4,"m")&" Months "&DATEDIF(E4,A4,"md")&" Days",DATEDIF(D4,A4,"m")&" Months "&DATEDIF(D4,A4,"md")&" Day(s) ")

 

[gsistek]

Having same problem...its wierd

 

[rex759]

Thank you all! I was going a little crazy as to why this was happening.

 

[Haseeb Avarakkan]

See the Barry Houdini's posts

http://www.mrexcel.com/forum/showthread.php?t=302028

 

[jrabi]

=YEAR(B2)-YEAR(A2)-(TEXT(B2,"mmdd")< TEXT(A2,"mmdd"))&" years "&MOD(MONTH(B2)-MONTH(A2)-(DAY(B2)< DAY(A2)),12)&" months "&B2-DATE(YEAR(B2),MONTH(B2)-(DAY(B2)< DAY(A2)),DAY(A2))&" days"

<TABLE style="WIDTH: 297pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=396><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=2 width=96><COL style="WIDTH: 153pt; mso-width-source: userset; mso-width-alt: 7460" width=204><TBODY><TR style="HEIGHT: 39pt" height=52><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 72pt; HEIGHT: 39pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=52 width=96>Beginning Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=96>Ending Date</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 153pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=204>Time Open</TD></TR>

column A column B column C
<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>1/31/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>1/30/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>0 years 11 months 30 days


</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=17>1/2/1979</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>4/24/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>32 years 3 months 22 days


</TD></TR></TBODY></TABLE>