Software

Prevent Excel from turning fractions into dates

Left to its own devices, Excel will convert your fractions into dates as soon as you enter them in your spreadsheet. See how you can work around this problem.

If you don't apply a fraction format to a cell before you enter a fraction into it, Excel will convert the fraction into a date. For example, if you enter 1/50 into a cell, Excel will convert it to Jan-50. If you then try to reformat the same cell to a fraction, Excel will convert it to 18264, the internal number that represents Jan-50. In that case, if you want Excel to recognize your cell entries as fractions, you must apply the appropriate fraction format to the cell before you enter the data.

For example, say you are importing a column of data from an Access database table that contains fractions such as 1/50, 2/70, and 30/65. You will need to format the column of cells containing the data to an appropriate fraction format before importing. Follow these steps:

  1. Select the column that will contain the fractional data.
  2. Right-click the selection.
  3. Select Format Cells.
  4. In the Number tab, under Category, select Fraction.
  5. Under Type, select Up To Two Digits (21/25), then click OK.

Now, when you import the data to that column, Excel will format each entry as a fraction. Keep in mind that if the data contained fractions such as 30/750, you would select Up To Three Digits (312/943) in Step 5. If you wanted to display your data as fractions over 100 (for example, 30/65 displayed as 46/100), you would select As Hundreds (30/100) in Step 5.

If you won't be using the fractions in calculations, you can prevent Excel from changing your entries to dates by applying the Text format to the cell before you enter the data. In that case, in Step 4 select Text under the Category list and then click OK.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

24 comments
mcadae
mcadae

Whoever at MicroSoft that set this functionality to default and not give a way to turn it off should be drug out to the street and shot!!!!  After being drug across a road full of glass and then dunked in a vat of rubbing alcohol.  (Yes I'm irritated about this and have given it a lot of thought)

ATTENTION MICROSOFT ENGINEERS USE EXCEL TOO!!!  WE ARE NOT ALL BEAN COUNTERS IT WORKED JUST FINE IN EVERY EXCEL UP TO 2007 WHY DID YOU BREAK IT????  I DON'T WANT A FK'N WORK AROUND I WANT IT TO WORK.  

touchdown_twc
touchdown_twc

Your tip did not work. Some of them displayed right but many did not. I had to go in and fight it to keep them fractions, this was crazy!

Mark A. Lewis
Mark A. Lewis

If the value is not used in calculations and is manually entered into Excel, using a single quote (i.e. '1/5) will also do the trick.

beccawhitey
beccawhitey

What about when you are exporting a file? There is not a way to format the Excel sheet because the data exports in Excel, but defaults to be saved as a .txt file...which I then have to save as an Excel file. Any suggestions? I need to keep the numbers as fractions when exporting them to double check that I have done everything properly but they export as dates!

nigel.bolton
nigel.bolton

The easiest solution I found for this fraction problem is always start with an = sign and Excel will treat the fraction as a fraction and not a date.

guy.drouin
guy.drouin

To enter a fraction that Excel can recognize, you have to precede your fraction with a zero and a space, for example 0 1/2 instead of 1/2. Excel will now recognize this entry as a fraction, store it's decimal equivalent in the cell and format it to display as a Fraction.

liljim
liljim

I have a recurring nightmare as I enter data into Excel(2003, SP3.) I am an accountant. I love a ten key adding machine key pad. But all my ten key skills are for naught when I enter date data. When I enter 011508, without a format it appears as 115.08. (Yes I used a fixed 2 decimal places.) I can set a column of data to a date format. But, if I format the column as a date, in the "03/14/01" style, the entry of 011508 converts to 04/24/00! WTF? I hate having to enter a whole column of dates with 2 "/" per cell. Data entry speed is shot to hell right away and my disposition is ruined for longer than it should. How about a tip to solve this problem, which I suspect haunts a lot more people than you'd ever imagine.

Pablo Antero
Pablo Antero

@touchdown_twc 

In Excel 2010 I paste using the alternative "leave the original format of the cell" and works fine

Regards

dhays
dhays

The use of a single quote does not necessarily preclude calcualtions, at least when the numbers represent dates. I use a spreadsheet (E2K3) where the dates are in a format '6/26/2009 and then I subtract one from the other to get the days between the dates, if it turns out to be the same day, I use an "if" function to set the value to 1. To take '1/2 and subtract '1/4 from it gives an overflow of negative dates or times are displayed as ####### type error. to go the other way '1/4-'1/2 (two cells) gives an answer of 1/2/1900, so Excel still treats them as dates, not fractions. Cell format is General Number. Setting the 1/2 and 1/4 cells as fractions and then subtracting gives a whole number (+ or -) depending on which way it is subtracted.

WLaddR
WLaddR

The nice thing about starting with a Zero is that the cell is automatically formatted as a fraction and displays as such. Using the = first converts your input to a decimal until you manually format the cell as a fraction. If you want to display as fractions the leading zero makes it easier to set any random cell to that format. Plus it keeps your hand on the keypad without having to go back to the keyboard for =

maria.grieser
maria.grieser

Of course you must still set the Number Format to "Fraction" with the "Up to 3 digits (312/943)" to get 3 digits to display. I love it! Thank you for the tip!

LocoLobo
LocoLobo

Didn't know that one. I usually enter fractions as =1/2 which displays the number 0.5

Bee Jay
Bee Jay

Most Ten Key folks find it very easy to hit the + and - signs without looking. Format your column as Date and then type your dates with a minus sign as in: 02-15-08 and voila! Excel still treats it as a date and shows 02/15/08.. problem solved...

Tanya L
Tanya L

Simply set a custom number format to the cells before you start of: 00"/"00"/"00 After that, just make sure you always enter 6 keys, being sure to type "01" and such for dates with only one number

Francis.Pillow
Francis.Pillow

You might try a CUSTOM FORMAT of the column. 0#\/##\/## seems to work, but I don't know if that'll work in a date calc later.

Architect
Architect

One way to do what you want is to format the column your entering the six digit date into as a text field (say column 'A' ). You can then convert the text number in a seperate column (column B) to date format. If cell A1 has '021308', the the formula in B1 would be =LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2) will convert the text number to 02/13/08. If you need the cell with the formula converted to a true date field, then in a third column formated for dates you can select the column with the text numbers in column B, right click and select 'Copy', move to the top of the column formated for dates, right click and select 'Paste Special' then choose 'Values' on the Radio Button and click 'Okay'. You then have a column of text numbers formated for dates. You can then hide or delete the columns with your original date entry and the calculation column.

sma_mark
sma_mark

I am having a similar issue. I would like to be able to enter 24 hour times simply by using 3 or 4 digits (i.e. - '805' for '8:05' or '1413' for '14:13'). However, no matter how I try to format the cell (before entering data), it always converts it to the serial date. Any ideas? Thanks...

DuhGreek
DuhGreek

An easier way of getting a true date value is to use the DATEVALUE function. Make your concatenated text functions the argument for DATEVALUE: =DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2)) You will need to apply date formatting to the cell; Otherwise it will display Excel?s internal date value for the date. The same would apply for time-entries, but use ":" instead of "/" and TIMEVALUE instead of DATEVALUE.

Brendan P
Brendan P

Yes, Excel can be a pain with time formats. I also ran into the 24 hour time entry problem. The MS recommended fix: make sure that the time format for the computer was 24 hour time. Yup... the whole system, it works and everyone on the PC has to work that way too. Another Excel time quirk try adding worked hours that total more than 24. It wants to format it as whatever remainder of a 24h day. The MS "fix": a custom format [hh]:mm;@ After all this "time" and program versions, you would think they would get it right. The number of related complaints on these issues is astounding.

jennyh1234
jennyh1234

I remember a earlier tip (that I can't find now) about adding code to a single worksheet for special formating of time for stuff like payroll. I use it for one of my payroll worksheets, because it is a hand written time sheet and it makes entering time so much easier. On the worksheet you need this on right click on the tab and select view code at the bottom. The code I use, just change the range to the cells you need this in. This works for 24 hour time - example: enter 1700 for 5pm and it will be displayed at 17:00, but the value in the cell is 5:00:00 PM. Hope this helps. Maybe some else can find the original tip. Private Sub Worksheet_Change(ByVal Target As Range) Dim vVal If Target.Cells.Count > 1 Then Exit Sub If Intersect(Target, Range("B2:E15")) Is Nothing Then Exit Sub With Target vVal = Format(.Value, "0000") If IsNumeric(vVal) And Len(vVal) = 4 Then Application.EnableEvents = False .Value = Left(vVal, 2) & ":" & Right(vVal, 2) .NumberFormat = "[h]:mm" End If End With Application.EnableEvents = True End Sub

helmma
helmma

You could input the hour in one column and the minutes in another column and then combine them in a third column (e.g. hour in B1; minute in C1, time = B1&":"&C1). I was surprised to find that I did not need to copy/paste special>values in order to calculate the difference between 2 values in the final time column.

Editor's Picks