Date format problem following cut/paste from Access

[Daniel Carollo]

Good Morning All!

I have the following problem:
After pasting a range of data with a column that contains dates, the date
format does not get taken in consideration until the data in the field is
actually edited.
For example, I can select the column, and apply a date format (let's say
dd-mmm-yyyy) but the data will still look the same on screen. If I then
double-click on a cell in the range, and then move away from that cell, it
then takes the format I specified.

Forcing a recalculation wiht F9 doesn't change that.

If I select the range and force a General format on it, I can see that the
undelying data is a date (and not text), sorting it produces correct results
(showing that Excel interprets that column as a date).

Does anybody know if the Office SP3 solves that problem, I couldn't figure
that out with the description of the changes in SP3 in the KB.

Thanks in advance for the help.

Daniel

 

[Frank Kabel]

Hi
one workaround. use the following macro (processes the selected area):
sub foo()
dim rng as range
set rng = selection
rng.value=rng.value
end sub

 

[Daniel Carollo]

Hello Frank!

First of all, thank you very much for your prompt answer.

I'm sure your workaround does work, as I did something similar to validate
my initial assumption and confirm it was indeed an Excel bug:
Dim c As Range, p As Range
Set p = Range(Range("A2"), Range("A2").End(xlDown))
For Each c In p.Cells
c.Value = CDate(c.Value)
Next c
Set p = Nothing

However, the issue still remains to find out whether this bug gets corrected
in SP3 or not, as there are a fair number of users around me that would not
be able to use your workaround.

Regards.

Daniel

 

[Frank Kabel]

Hi
this is not a bug but just a behaviour of importing data. How should
Excel know that you really want date values and not 'Text' values. If
Excel would do it the other way round I'm sure that other people would
complain about this behaviour as well

That said: An option to control this would be great )