Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format problem following cut/paste from Access
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 :-) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format problem following cut/paste from Access
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" wrote: 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 :-) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date format problem following cut/paste from Access
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" wrote: 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" wrote: 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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy paste format problem | New Users to Excel | |||
Problem with the format of cells after impoting from Access | Excel Discussion (Misc queries) | |||
Date Format Issues Access to Excel | Excel Discussion (Misc queries) | |||
How to get only the year in the date format in Access | New Users to Excel | |||
cut and paste format problem | Excel Discussion (Misc queries) |