by "them" i mean all dates contained in the workbook.
when the raw data is imported it is in UK format (dd/mm/yyyy).
i have tried both the suggestions but neither works.
this is driving me mad
You have not really supplied enough information to advise you further. And I'm
not sure you are understanding exactly what it is that Excel is doing with the
dates.
The problem is (most likely) that your dates are not all truly dates; but that
some of them are textual representations of dates; and others are "real" dates
(i.e. serial numbers with 1 = 1/1/1900 formatted to look like a date).
So, for example, and possibly depending on the users regional settings, and the
method of data entry/importation, you might have sequential cells which appear
as:
A1: 12/08/2005
A2: 13/08/2005
If this data were, for example, typed into a machine with USA regional
settings, the first would get translated to 8 Dec 2005 and the serial number
38694 would be stored in A1. The second would be entered as a TEXT string and
would appear correct to you, but would not be an Excel date that you could do
comparisons on.
Your template comparisons will therefor fail.
-------------------------------------
These kinds of issues cannot be resolved AFTER the data has been placed into
the Excel worksheet. They MUST be resolved PRIOR to that point (or at least be
setup properly before).
-----------------------------------
In addition, if your user is going to be entering any dates at all, unless they
are entered as text, Excel will parse the entry according to the user's Windows
regional settings -- this is not something you are likely to have control over.
----------------------------------
My suggestion would be to ensure that all dates are true Excel dates (serial
numbers). This can be done in a variety of ways.
1. Ensure that the date fields in RawData are unambiguous: i.e. instead of
12/08/2005 output 12 Aug 2005. The Excel parser will then convert this to a
real date, and you can have your worksheet formatted to display UK style dates.
2. If the above is not possible, import the data as a TEXT file (i.e. with a
..txt suffix). This will bring up the Text-to-Columns wizard (can also be done
in VBA) which will allow you to specify, prior to import, the order of the date
fields. (Select Data/Text to columns to see what I'm talking about).
You could format all cells as TEXT prior to importing or entering any
data. This is probably the least flexible method and might cause difficulties
for date data input by folk not used to the UK style. Although you might be
able to use data validation to ensure proper date entry.
--ron
