Sunday, January 02, 2005

Fractions versus Dates in Excel

Ian writes about the problem Excel has with determining 1/4 as either a date or a fraction.

By adding a zero to the front of the fraction Excel will know for certain that "0 1/4" should be a fraction instead of January 4th.

He also details the formatting requirements to make Excel format numbers that way you want.
For example when you change something like 4.235 to display as a fraction, "4 1/4" is displayed. Click Format | Cells | Number tab and then choose Fraction from the list of number formats. A list of fraction display choices will pop up, allowing you to change from the default of one fraction digit to two or three, turning 4.235 into "4 4/17" or "4 47/200," respectively.


Posted by: Jethro | 1:41 PM |