Hi

I have an MS Access report that I copy+paste into an excel worksheet. Other
cells in the worksheet contain conditional formatting which lookup the cells
with the access data (date: dd/mm/yyyy) and compares against a date in two
other cells and shades the cell appropriately.

The problem is not the formula - it works fine - but that the conditional
formtting does not work on the data exported from Access but only works if I
manually type in a date. However, I cannot see ANY difference between an
manually tpyed in date and the Access exported date.

Help please!
Mark

Perhaps this might work for you ..

Select the column of "dates"
Data Text to Columns
Click Next Next
In step 3, under "Column data format":
Check "Date" and select the format "DMY" from the droplist
Click Finish
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Mark A" <Mark wrote in message
...
Hi

I have an MS Access report that I copy+paste into an excel worksheet.
Other
cells in the worksheet contain conditional formatting which lookup the
cells
with the access data (date: dd/mm/yyyy) and compares against a date in two
other cells and shades the cell appropriately.

The problem is not the formula - it works fine - but that the conditional
formtting does not work on the data exported from Access but only works if
I
manually type in a date. However, I cannot see ANY difference between an
manually tpyed in date and the Access exported date.

Help please!
Mark

Thanks. That works - though I don't really understand why.
It is a shame that whatever formatting Excel applied when you run "Text to
Columns" doesn't persist in the worksheet. i.e. when I paste in new data from
Excel I have to run Text to Columns again. Not a big problem - I have created
a macro to do this and stuck a button in the menu bar.

Thanks verymuch for the v. quick reply!

"Max" wrote:

Perhaps this might work for you ..

Select the column of "dates"
Data Text to Columns
Click Next Next
In step 3, under "Column data format":
Check "Date" and select the format "DMY" from the droplist
Click Finish
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Mark A" <Mark wrote in message
...
Hi

I have an MS Access report that I copy+paste into an excel worksheet.
Other
cells in the worksheet contain conditional formatting which lookup the
cells
with the access data (date: dd/mm/yyyy) and compares against a date in two
other cells and shades the cell appropriately.

The problem is not the formula - it works fine - but that the conditional
formtting does not work on the data exported from Access but only works if
I
manually type in a date. However, I cannot see ANY difference between an
manually tpyed in date and the Access exported date.

Help please!
Mark

Glad it worked, Mark !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Mark A" wrote in message
...
Thanks. That works - though I don't really understand why.
It is a shame that whatever formatting Excel applied when you run "Text to
Columns" doesn't persist in the worksheet. i.e. when I paste in new data
from
Excel I have to run Text to Columns again. Not a big problem - I have
created
a macro to do this and stuck a button in the menu bar.

Thanks verymuch for the v. quick reply!

I have now hit another wall in terms of my know-how.
I now have a number of excel spreadhseets with the macro and toolbar button
I created as described before.
I want to save the spreadhseets on a shared drive for colleagues to use. How
do I associate the menu button and macro with these spreadhseets? If I copy
the files locally then the spreadhsset can no longer find the macro.
other psots/websites suggest using an .xlt or addin. But I cannot get former
to work nor figure out the latter.

Thanks again
Mark

"Max" wrote:

Glad it worked, Mark !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Mark A" wrote in message
...
Thanks. That works - though I don't really understand why.
It is a shame that whatever formatting Excel applied when you run "Text to
Columns" doesn't persist in the worksheet. i.e. when I paste in new data
from
Excel I have to run Text to Columns again. Not a big problem - I have
created
a macro to do this and stuck a button in the menu bar.

Thanks verymuch for the v. quick reply!

Sorry I don't know, Mark.
Hang around for insights from others ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--