Hi Don,
You asked how to identify the cells. Changing the alignment temporarily as
suggested is one way to do that. Another way is via the ISNUMBER worksheet
function. A 'text' date will return FALSE.
You obviously didn't try multiplying the 'text' dates by 1.
Type 1 in any cell.
Copy the 1 to the clipboard.
Select a 'text' date.
Choose Edit|Paste Special|Multiply.
You should now have a serial number displayed. This is the number in Excel
that corresponds to your date.
Format this cell in your preferred date format and you will see your date
again - this time stored as a number.
"Don Wiss" wrote in message
...
On Fri, 9 Jul 2004 20:38:49 +1000, "macropod" wrote:
Don Wiss wrote:
Some of our users are getting data from clients and pasting the data
into
the spreadsheet. Sometimes the years are text. Even though the cell is
formatted as a number, being text it has a value of 0 to any cell that
references it. If I use a macro to test its value it gives me a number.
If
I can find a way to detect this situation I can issue a
Range("YearColumn").Value = Range("YearColumn").Value
when deactivating the sheet. Or maybe just always do this when
deactivating?
Visually, if the column is formatted as general, 'text' dates will be
left
aligned, while 'numeric' dates will be right aligned.
But we have formatted the column as centered.
Making sure all dates
are numeric, is as simple as multiplying them by 1. This will convert the
'text' dates to 'numeric' date equivalents, though you may lose the
cell's
date formatting in the process if you do it in the cell.
This will not work. As the dates are text they have a numeric value of 0.
Multiplying anything against them leaves their value at 0.
What I have done is every time the sheet is deactivated I simply run this
line: Range("YearColumn").Value = Range("YearColumn").Value
It works fine. While there are some on screen numbers that don't appear
while still on the sheet, all critical numbers are on later sheets, and
running this line makes them all correct. And when they return, the
calculated numbers on the input sheet will then appear.
Don <donwiss at panix.com.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 2/07/2004