Please Help

[threepick]

I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"

C7 3/24/04
D7 1/4/08

What is returned is 3 years, 9 months, 136 days
How might if fix it show the correct days?

 

[VoG]

Hello and welcome to MrExcel.

What is in C7 and D7?

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

 

[threepick]

c7 3/12/04
d7 1/4/08

 

[threepick]

Sorry I forgot to enter the cell contents

c7 3/12/04
d7 1/4/08

 

[VoG]

Sorry, are they d/m/y or m/d/y?

 

[Robert Mika]

What's values of C7 and D7?

 

[VoG]

Having read your edited post #1 I guess that you are using m/d/y date format. Please bear in mind that I am using d/m/y. This is my result

Sheet4

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 102px;"><col style="width: 97px;"><col style="width: 24px;"><col style="width: 206px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td> </td><td style="border: 3px outset rgb(240, 240, 240);">C</td><td style="border: 3px outset rgb(240, 240, 240);">D</td><td style="border: 3px outset rgb(240, 240, 240);">E</td><td style="border: 3px outset rgb(240, 240, 240);">F</td></tr><tr style="height: 18px;"><td style="border: 3px outset rgb(240, 240, 240); background-color: rgb(202, 202, 202); text-align: center;">7</td><td style="text-align: right;">24/03/2004</td><td style="text-align: right;">04/01/2008</td><td> </td><td>3 yrs, 9 mths, 124 days</td></tr></tbody></table>
<table style="border-style: groove; border-color: rgb(0, 255, 0); font-family: Arial; font-size: 10pt; background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>F7</td><td>=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"</td></tr></tbody></table></td></tr></tbody></table>
Excel tables to the web - Excel Jeanie Html 4

 

[threepick]

I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"

C7 3/24/04
D7 1/4/08


What is returned is 3 years, 9 months, 136 days
How might if fix it show the correct days?

the dates are in m,d,y

 

[threepick]

I am using the following formula to calculate years months and days in Excell 2007
=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"

C7 3/24/04
D7 1/4/08


What is returned is 3 years, 9 months, 136 days
How might if fix it show the correct days?

the dates are m,d,y
the days should not be => than 1 month

 

[VoG]

the dates are m,d,y
the days should not be => than 1 month

Yes of course they shouldn't

I think you've found another bug - take a look at the below comparing DATEDIF with barry's formula from the link I posted above.

<b>Sheet4</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:102px;" /><col style="width:97px;" /><col style="width:24px;" /><col style="width:206px;" /><col style="width:239px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">C</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">D</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">E</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">F</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">G</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td >DATEDIFF</td><td >BARRY'S FORMULA</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">04/01/2008</td><td > </td><td >3 yrs, 9 mths, 124 days</td><td >3 years 9 months 11 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">03/01/2008</td><td > </td><td >3 yrs, 9 mths, 123 days</td><td >3 years 9 months 10 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">02/01/2008</td><td > </td><td >3 yrs, 9 mths, 122 days</td><td >3 years 9 months 9 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">01/01/2008</td><td > </td><td >3 yrs, 9 mths, 121 days</td><td >3 years 9 months 8 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">31/12/2007</td><td > </td><td >3 yrs, 9 mths, 7 days</td><td >3 years 9 months 7 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">30/12/2007</td><td > </td><td >3 yrs, 9 mths, 6 days</td><td >3 years 9 months 6 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">29/12/2007</td><td > </td><td >3 yrs, 9 mths, 5 days</td><td >3 years 9 months 5 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">28/12/2007</td><td > </td><td >3 yrs, 9 mths, 4 days</td><td >3 years 9 months 4 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">27/12/2007</td><td > </td><td >3 yrs, 9 mths, 3 days</td><td >3 years 9 months 3 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">26/12/2007</td><td > </td><td >3 yrs, 9 mths, 2 days</td><td >3 years 9 months 2 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">25/12/2007</td><td > </td><td >3 yrs, 9 mths, 1 days</td><td >3 years 9 months 1 days</td></tr><tr style="height:18px ;" ><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">24/03/2004</td><td style="text-align:right; ">24/12/2007</td><td > </td><td >3 yrs, 9 mths, 0 days</td><td >3 years 9 months 0 days</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>Spreadsheet Formulas</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 >F7</td><td >=DATEDIF(C7,D7,"y") & " yrs, " & DATEDIF(C7,D7,"ym") & " mths, " & DATEDIF(C7,D7,"md") & " days"</td></tr><tr><td >G7</td><td >=YEAR(D7)-YEAR(C7)-(TEXT<span style=' color:008000; '>(D7,"mmdd")</span>< TEXT<span style=' color:008000; '>(C7,"mmdd")</span>)&" years "&MOD(MONTH<span style=' color:008000; '>(D7)</span>-MONTH<span style=' color:008000; '>(C7)</span>-<span style=' color:008000; '>(DAY<span style=' color:#0000ff; '>(D7)</span>< DAY<span style=' color:#0000ff; '>(C7)</span>)</span>,12)&" months "&D7-DATE(YEAR<span style=' color:008000; '>(D7)</span>,MONTH<span style=' color:008000; '>(D7)</span>-<span style=' color:008000; '>(DAY<span style=' color:#0000ff; '>(D7)</span>< DAY<span style=' color:#0000ff; '>(C7)</span>)</span>,DAY<span style=' color:008000; '>(C7)</span>)&" days"</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4