12ths denominators for fractions in Excel (for pension calcs)

[Guest]

As a pensions administrator, I use Excel to display pensionable service in
years and complete months. The ability to express service in terms of years
and twelfths of a year would be useful. This is not one of the options for
fraction formatting.

In addition, pension calculations often require a period of service to
becalculated between two dates (joining and leaving). This is often
expressed in terms of years and months, where the months are not always
complete calendar months, but a period of 28 to 31 days straddling a month
end. eg 15 Jul 2000 to 14 Aug 2006 inclusive would be 6 years and 1 month.
As a refinement, 31 Dec 2000 to 28 Feb 2006 would be 5 years and 2 months.

Could a function be devised to calculate the number of complete years and
months? Variations would be years and weeks, and years and days, possibly
years/months/days and years/weeks/days.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...dg=microsoft.public.excel.worksheet.functions

 

[Bernard Liengme]

The 12th fraction is not included in the Fraction part of Format but if you
go the Custom and type # ?/12 you will get what you want.

As for computing differences between two dates, you need the undocumented
(well it was documented in XL2000, I believe) function DATEDIFF. See
http://www.cpearson.com/excel/datedif.htm for all you need to know on that
topic.

best wishes

 

[therealtiger]

Now how to concatenate that nice fraction to other text without it reverting back to a decimal?