Your "dates" are probably text. You'll need to convert them to real numbers, before formatting them.
One common reason formulas don't work is if the "numbers" or "dates" are text that looks like numbers or dates. You can test this possibility by trying to change the format of the cell. If this works, then the cell contains a number or date/time serial number. If it doesn't, then you need to convert them. Changing the format from text to a number format won't change the data--it will still be text as you can see from a formula like:
=ISTEXT(A1) returns TRUE if value is text (even if cell is formatted as a number and the contents look like a number)
=ISNUMBER(A1) returns TRUE if value really is a number (even if cell is formatted to display text like Friday, January 1, 2005)
A few easy ways to convert them are:
1) Copy a blank cell
2) Select the cells to convert
3) Edit...Paste Special...Add
4) Format the cells as desired
A macro to do this is quite simple. It goes in a regular module sheet. To use it, select the cells to be converted, then run the macro.
Sub TextToNumbers()
Dim rg As Range
On Error Resume Next
Set rg = Selection
rg.NumberFormat = "General"
rg.Value = rg.Value
On Error GoTo 0
End Sub
Another way to convert text to numbers uses the Data...Text to Columns menu item. One benefit of this approach is that you can specify the format of dates (such as if they were exported with leading zeros as mmddyy). You can also throw away part of the data (if you want).
1) Select the cells to be converted
2) Open the Data...Text to Columns menu item
3) In the first step of the wizard, choose "Delimited" ("Fixed if you want to get rid of certain information)
4) Click "Next" twice
5) In the third step of the wizard, you can specify whether a column contains text or dates. Choose General if the column contains numbers. If you choose Dates, specify the format in the dropdown to the right.
6) Click "Finish"
Main Topics
Browse All Topics
by: zorvekPosted on 2007-04-04 at 10:54:55ID: 18852367
You are saying that when you select the cell you see "3/16/2007"? Where? In the cell or in the formula entry field?
Kevin