How to show yrs and months in a pivot table

[yellowcabguy]

I am calculating the length of time a dog is at a shelter. The date the dog arrives is compared to the current date; that number is divided by 365 and I get things like 1.3 years or 1.5 years,etc.

However I want to show that number as years and months. So 1.5 years would actually be 1 years 6 months.

Is there a way to do that??

 

[Peter_SSs]

Something like this?

Formula in C1 (copied down):
=DATEDIF(A1,B1,"y")&" yrs and "&DATEDIF(A1,B1,"ym")&" months"

Mr Excel.xls
	A	B	C	D
1	15/06/2005	13/09/2006	1 yrs and 2 months
2	04/04/2003	15/08/2006	3 yrs and 4 months
3	08/09/2003	15/10/2003	0 yrs and 1 months
4
DATEDIF

 

[becken]

Alternatively you could choose Format_Cell, go to the Custom option, select # ??/??, and change it to # ??/12.

This would then display your 1.5, as 1 6/12. You don't have to put in an extra column with a formula then.

 

[yellowcabguy]

years and months in Pivot

Think I need to add some additional info to my first post.

Here is what I have

Column G is IN DATE (displayed as 6/15/2005)-reflects the date the dog enters the shelter

IF the dog is adopted there is a date in Column Z (displayed as 9/12/2006) this reflects the date the dog left the shelter

In this case the length of time (in yr,months)would be shown in Column AM (a compare between Columns G & Z)

HOWEVER if the dog is not adopted (hence column Z is blank) than the length of time at the shelter is determined by a comparison of Column G date (IN DATE) to the current date. I have that stored in cell A2.

Does this make it any clearer??

 

[becken]

I think if you just amend your formula to:
(Date joined - if(date adopted="",A2,date adopted))/365

And then use either my method to format the cells, or it shouldn't be too difficult to amend Peter's formula on the same principles using an if statement.

Hope this helps.