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

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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.
 

Forum statistics

Threads
1,141,011
Messages
5,703,722
Members
421,311
Latest member
tanujath

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top