dates to converted into years & months

If you want the time difference from a date in A2 until today then try this formula in B2

=DATEDIF(A2,TODAY(),"y")& " years "&DATEDIF(A2,TODAY(),"ym")&" months"

Thanks for the solution, but i want years in a separte cell and month in a separate cell. How to write/ change the formula. Please convey. Sincere thanks for the immediate reply within a fraction of second yesterday.

You can use 2 DATEDIF functions in separate cells, i.e. in one cell for the years

=DATEDIF(A2,TODAY(),"y")

and another for the remaining months

=DATEDIF(A2,TODAY(),"ym")

DATEDIF function is not very well documented in Excel help, if at all. See here for more on DATEDIF

I want the date to be calculated for the future date say for eg. as on 30-05-2010, or as on 30-06-2010, what is the formula I have to enter. Please guide me . I have got the answer taking this day, but not the future date.

Place your future date in a fixed cell and in your function replace the today() with that cell's reference..

e.g. you have placed your future date/as on date to D1

so your two functions would be

=DATEDIF(A2,$D$1,"y")


=DATEDIF(A2,$D$1,"ym")

Sincere Thanks for the immediate response.

By making use of the formula, I got the results, now the next problem which I am facing is
1. I have put the future date in a separte column, and I got years and months in separte columns. I have sorted the data, the results I am getting is in ascending order. say for eg. 2 years 10 months , 3 years 1 months etc., but what I want is it should be in descending order. So what I have to do?

Thanks in advance for the solution provider.

I am herewith attaching the file how the results have come, what I want is the other way. i.e. the highest number of years should be first and least year should be last.
Sheet1


<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 11pt; FONT-FAMILY: Calibri,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 138px"><COL style="WIDTH: 64px"><COL style="WIDTH: 118px"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 89px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD><TD>M</TD><TD>N</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD>SL.NO. </TD><TD>BRANCH</TD><TD>CAPACITY</TD><TD> </TD><TD>DATE OF SUP</TD><TD> </TD><TD> </TD><TD>FUTURE DATE</TD><TD>YEARS</TD><TD>MONTHS</TD></TR><TR style="HEIGHT: 19px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="TEXT-ALIGN: right">8</TD><TD style="FONT-SIZE: 12pt">BANASHANKARI</TD><TD style="FONT-SIZE: 12pt">2KVA</TD><TD style="FONT-SIZE: 12pt"> </TD><TD style="FONT-SIZE: 12pt; TEXT-ALIGN: right">26-Jul-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">2</TD><TD style="TEXT-ALIGN: right">11</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="TEXT-ALIGN: right">2</TD><TD>ANNUR</TD><TD>1KVA</TD><TD>ASPATHREKAVAL</TD><TD style="TEXT-ALIGN: right">2-Jan-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="TEXT-ALIGN: right">5</TD><TD>BANDAHALLY</TD><TD>1KVA</TD><TD> </TD><TD style="TEXT-ALIGN: right">7-Feb-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="TEXT-ALIGN: right">7</TD><TD>BOMMALAPURAA</TD><TD>1KVA</TD><TD> </TD><TD style="TEXT-ALIGN: right">13-Feb-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">4</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="TEXT-ALIGN: right">8</TD><TD>BOMMALAPURAA</TD><TD>1KVA</TD><TD>ALATHUR</TD><TD style="TEXT-ALIGN: right">16-Jun-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="TEXT-ALIGN: right">9</TD><TD>BYLAHALLY</TD><TD>1KVA</TD><TD> </TD><TD style="TEXT-ALIGN: right">7-Mar-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="TEXT-ALIGN: right">10</TD><TD>BYLAHALLY</TD><TD>1KVA</TD><TD>PERIYAPATNA</TD><TD style="TEXT-ALIGN: right">7-Mar-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">3</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="TEXT-ALIGN: right">11</TD><TD>CHANDRAVADI</TD><TD>1KVA</TD><TD>KYATHANAHALLY</TD><TD style="TEXT-ALIGN: right">16-Jun-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="TEXT-ALIGN: right">18</TD><TD>GUNDLUPUET</TD><TD>1KVA</TD><TD>HANGALA</TD><TD style="TEXT-ALIGN: right">2-Jan-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">5</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="TEXT-ALIGN: right">19</TD><TD>H.D.KOTE</TD><TD>1KVA</TD><TD>BEECHANAHALLY</TD><TD style="TEXT-ALIGN: right">16-Jun-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 18px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="TEXT-ALIGN: right">20</TD><TD>HAMPAPURA HDK</TD><TD>1KVA</TD><TD> </TD><TD style="TEXT-ALIGN: right">25-Jun-07</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">3</TD><TD style="TEXT-ALIGN: right">0</TD></TR></TBODY></TABLE>

Sincere thanks for the solution provider.
Excel tables to the web >> Excel Jeanie HTML 4

Select the data including headings, in your case from A1 to N12..

Go to Data menu>Choose Sort>It will open the Sorting window, in first drop down list choose column M and click on the radio button "Descending" and in second list i.e. "Then by" choose column N and hit the "Descending" radio button.. now click on 'OK'..

That's it, you can chage your Sorting criateria according to your need..