Discussion on:

Prevent Excel from turning fractions into dates

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

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.

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

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

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.

You can try downloading a Ready to use Excel Timesheet Template from http://www.calculatehours.com/

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.

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.

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.

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

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.

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

Good advice !!

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.

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!

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.

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!