# Discussion on: Prevent Excel from turning fractions into dates

22

### Join the conversation!

View:
Show:
###### My gripe as I slowly enter data into Excel.
liljim@... 13th Feb 2008
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.
###### I agree..help with date/time entry!
sma_mark 13th Feb 2008
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...
###### Concatenate hours and minutes to form your time
helmma 13th Feb 2008
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.
###### There is a way
jennyh1234 13th Feb 2008
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
###### Very Comprehensive Solution
sma_mark 11th Nov 2008
I don't know if this is the actual solution you were looking for, but it covers quick entry of both date and time:

http://www.cpearson.com/excel/DateTimeEntry.htm
###### Excel time format - time as duration
Brendan P 27th May 2009
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.
###### Number as Text to Date
Architect 13th Feb 2008
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.
###### Number as Text to Date
DuhGreek 13th Feb 2008
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.
###### Date slashes
Francis.Pillow@... Updated - 13th Feb 2008
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.
###### Simple solution
Tanya L 14th Feb 2008
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
###### Minus key easier to type than the / key.
Bee Jay 15th Feb 2008
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...
###### Another Solution...
guy.drouin@... 13th Feb 2008
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.
###### Cool
LocoLobo 13th Feb 2008
Didn't know that one. I usually enter fractions as =1/2 which displays the number 0.5
###### Nice tip!
maria.grieser@... 13th Feb 2008
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!
###### RE: Prevent Excel from turning fractions into dates
nakukuku@... 14th Feb 2008
nigel.bolton@... Updated - 14th Feb 2008
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.
###### Zero vs equals
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 =
###### RE: Prevent Excel from turning fractions into dates
beccawhitey@... 10th Nov 2008
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!
###### RE: Prevent Excel from turning fractions into dates
Mark A. Lewis 27th May 2009
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.
###### fractions, etc
dhays 27th May 2009
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.
###### fractions to dates
touchdown_twc 15th May
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!
Keyboard Shortcuts:
Prev
Next
Toggle
###### Join the conversation
Formatting +
BB Codes - Note: HTML is not supported in forums
• [b] Bold [/b]
• [i] Italic [/i]
• [u] Underline [/u]
• [s] Strikethrough [/s]
• [q] "Quote" [/q]
• [ol][*] 1. Ordered List [/ol]
• [ul][*] · Unordered List [/ul]
• [pre] Preformat [/pre]
• [quote] "Blockquote" [/quote]

Join the TechRepublic Community and join the conversation! Signing-up is free and quick, Do it now, we want to hear your opinion.