Excel dates not filtering in Word: Office 07

B

Boppy

Hi folks, I have posted this problem in the Word group without
success, and also in the Mr Excel bulletin board, also without finding
a solution. I wonder if anyone here can help:

I do regular mail merges in Word using data created in Excel. I am
almost always wanting to filter by the date the record was entered
into Excel, and which was recorded by typing ctrl ; to insert the
current date.

The problem is, when I am filtering the data in Word, and type in the
date I want the records from, I get this error message:
http://i30.tinypic.com/2uz40h4.jpg

Word happily filters dates that I have typed into a table in Word then
used as a data source.

What do I need to do to have Word recognise my Excel dates?

In case this is relevant, my regional settings are:
http://i193.photobucket.com/albums/z...screenshot.jpg

Thanks in advance for advice,

Jo
 
T

Tyro

Excel maintains dates as numbers. Excel's date range is from Jan. 1, 1900,
day 1, to Dec. 31 9999, day 2,958,465. Word does not maintain dates as
numbers. Word maintains dates as text. Excel simply formats the numbers to
display as a human understandable date.

Tyro
 
B

Boppy

Excel maintains dates as numbers. Excel's date range is from Jan. 1, 1900,
day 1, to Dec. 31 9999, day 2,958,465. Word does not maintain dates as
numbers. Word maintains dates as text. Excel simply formats the numbers to
display as a human understandable date.

Tyro

Yes, I realise that. Is there any way to filter dates though?
 
D

David Biddulph

Your best bet would probably be to filter in Excel, rather than in Word.
Another option is to use a helper column with =TEXT(A2,"m/dd/yyyy")
 
B

Boppy

Your best bet would probably be to filter in Excel, rather than in Word.
Another option is to use a helper column with =TEXT(A2,"m/dd/yyyy")
Thanks. I have been filtering in Excel, creating a new worksheet with
that data, then mail merging with that sheet. It is a bit annoying
that it's necessary to find workarounds when dealing with products
from the same developer though. Surely I am not the first person to
have this requirement ...
B
 
D

Debra Dalgleish

A date filter should work in Word, for Excel data. What solutions were
suggested in the other groups, that didn't work?
Were you able to use the date filter in a previous version of Word?

I can't see what your regional settings are, but from the Excel screen
shot it looks like m/d/yyyy is your short date. You could try mm/dd/yyyy
instead, to see if that helps.
You might want to blur your clients' personal data in your screen shot
though. They might not appreciate having it posted publicly.
 
B

Boppy

A date filter should work in Word, for Excel data. What solutions were
suggested in the other groups, that didn't work?
Were you able to use the date filter in a previous version of Word?

I can't see what your regional settings are, but from the Excel screen
shot it looks like m/d/yyyy is your short date. You could try mm/dd/yyyy
instead, to see if that helps.
You might want to blur your clients' personal data in your screen shot
though. They might not appreciate having it posted publicly.

Hi Debra, thanks for replying. There were no suggestions made in the
other groups at all, other than a work around similar to what I was
already doing.
When you say "You could try mm/dd/yyyy instead, to see if that
helps." do you mean when filtering in word, or do you mean I should
try setting my regional settings to this?

B
 
D

Debra Dalgleish

I meant that you could change the short date in the regional settings to
mm/dd/yyyy, and see if that helps with the filter in Word.
 
B

Boppy

I meant that you could change the short date in the regional settings to
mm/dd/yyyy, and see if that helps with the filter in Word.

In New Zealand we use dd/mm/yy so there would be no point trying mm/dd/
yy because it would totally confuse things. Do you think it's worth
trying dd/mm/yy?

B
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top