How to show yrs and months in a pivot table

yellowcabguy

Active Member
Joined
Dec 28, 2005
Messages
440
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??
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

becken

New Member
Joined
Mar 13, 2006
Messages
24
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

Active Member
Joined
Dec 28, 2005
Messages
440
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

New Member
Joined
Mar 13, 2006
Messages
24
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,915
Messages
5,545,016
Members
410,646
Latest member
LegenDSlayeR
Top