formula for calculating dates

[macyrasia]

hi,,is there any body who can help me to calculate these:

<table border="0" cellpadding="0" cellspacing="0" width="276"><col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="mso-width-source:userset;mso-width-alt:4278;width:88pt" width="117"> <tbody><tr style="mso-height-source:userset;height:39.75pt" height="53"> <td class="xl67" style="height:39.75pt;width:57pt" height="53" width="76"> a</td> <td class="xl67" style="border-left:none;width:62pt" width="83"> b</td> <td class="xl68" style="border-left:none;width:88pt" width="117"> c</td> </tr> <tr style="mso-height-source:userset;height:41.25pt" height="55"> <td class="xl67" style="height:41.25pt;border-top:none; width:57pt" height="55" width="76">9-Dec-07</td> <td class="xl67" style="border-top:none;border-left:none;width:62pt" width="83">11-Sep-08</td> <td class="xl68" style="border-top:none;border-left:none;width:88pt" width="117">=9mos&2days</td> </tr> <tr style="mso-height-source:userset;height:39.75pt" height="53"> <td class="xl67" style="height:39.75pt;border-top:none; width:57pt" height="53" width="76">6-Dec-08</td> <td class="xl67" style="border-top:none;border-left:none;width:62pt" width="83">31-Aug-09</td> <td class="xl68" style="border-top:none;border-left:none;width:88pt" width="117">=8mos,25days</td> </tr> <tr style="mso-height-source:userset;height:39.75pt" height="53"> <td class="xl67" style="height:39.75pt;border-top:none; width:57pt" height="53" width="76">3-Dec-09</td> <td class="xl67" style="border-top:none;border-left:none;width:62pt" width="83">6-May-10=</td> <td class="xl68" style="border-top:none;border-left:none;width:88pt" width="117">5mos,3days</td> </tr> <tr style="mso-height-source:userset;height:39.75pt" height="53"> <td class="xl67" style="height:39.75pt;border-top:none; width:57pt" height="53" width="76">7-May-10</td> <td class="xl67" style="border-top:none;border-left:none;width:62pt" width="83">5-Sep-10=</td> <td class="xl68" style="border-top:none;border-left:none;width:88pt" width="117">3mos,28days</td> </tr> </tbody></table>
i want to automatically calculate the column c..please help me ;(

 

[VoG]

Try in C1 copied down

=DATEDIF(A1,B1,"m")&" m " & DATEDIF(A1,B1,"md")& " d"

 

[macyrasia]

thanks VoG, thats the formula i used in column c..what i want to do is to calculate the sum of all datas in column c...the total value of months and date,,can u help me...please

 

[VoG]

You would really need 2 columns to do that:

Excel Workbook
	A	B	C	D
1	09/12/2007	11/09/2008	9	2
2	06/12/2008	31/08/2009	8	25
3	03/12/2009	06/05/2010	5	3
4	07/05/2010	05/09/2010	3	29
5
6			25	59
Sheet4

 

[macyrasia]

Thanks again,,the only problem for that is 59 days is equivalent to 1 month and 29 days,,,

 

[VoG]

If you are happy with the approximation that there are 30 days in a month then

Excel Workbook
	A	B	C	D
1	09/12/2007	11/09/2008	9	2
2	06/12/2008	31/08/2009	8	25
3	03/12/2009	06/05/2010	5	3
4	07/05/2010	05/09/2010	3	29
5
6			25	29
Sheet4

 

[macyrasia]

VoG, Im really sorry for these, i really appreciate your help...

Spreadsheet Formulas<table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>C6</td><td>=SUM(C1:C4)+INT(D6/30)</td></tr><tr><td>D6</td><td>=MOD(SUM(D1:D4),30)</td></tr></tbody></table> if i will use these formula,, there will be a missing months,, because the total is
<table style="width: 422px; height: 150px;" border="1" cellpadding="0" cellspacing="0"><tbody><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; ">A</td><td style=" border-width:3px; border-styleutset;border-color:#f0f0f0; ">B</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></tr><tr style="height:17px ;"><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; ">1</td><td style="text-align:right; ">09/12/2007</td><td style="text-align:right; ">11/09/2008</td><td style="text-align:right; ">9</td><td style="text-align:right; ">2</td></tr><tr style="height:17px ;"><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; ">2</td><td style="text-align:right; ">06/12/2008</td><td style="text-align:right; ">31/08/2009</td><td style="text-align:right; ">8</td><td style="text-align:right; ">25</td></tr><tr style="height:17px ;"><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; ">3</td><td style="text-align:right; ">03/12/2009</td><td style="text-align:right; ">06/05/2010</td><td style="text-align:right; ">5</td><td style="text-align:right; ">3</td></tr><tr style="height:17px ;"><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; ">4</td><td style="text-align:right; ">07/05/2010</td><td style="text-align:right; ">05/09/2010</td><td style="text-align:right; ">3</td><td style="text-align:right; ">29</td></tr><tr style="height:17px ;"><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; ">5</td><td> </td><td> </td><td> </td><td> </td></tr><tr style="height:17px ;"><td style="border-width:3; border-styleutset;border-color:#f0f0f0; background-color:#cacaca; text-align:center; ">6</td><td> </td><td> </td><td style="text-align:right; ">25</td><td style="text-align:right; ">59</td></tr></tbody></table>

26 months and 29 days..

 

[VoG]

Sorry, corrected formula

Excel Workbook
	A	B	C	D
1	09/12/2007	11/09/2008	9	2
2	06/12/2008	31/08/2009	8	25
3	03/12/2009	06/05/2010	5	3
4	07/05/2010	05/09/2010	3	29
5
6			26	29
Sheet4

 

[macyrasia]

thank you very much VoG