# How to show yrs and months in a pivot table

#### yellowcabguy

##### Active Member
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??

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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.

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??

I think if you just amend your formula to:

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.

Replies
0
Views
215
Replies
3
Views
176
Replies
3
Views
144
Replies
0
Views
164
Replies
2
Views
183

1,219,578
Messages
6,149,096
Members
450,859
Latest member
njaitley

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back