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 :-) |
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 :-) |
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 :-) |
All times are GMT +1. The time now is 11:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com