keeping dates in UK format

thanks,

the raw data is pasted into a worksheet inside the template and the records are filtered using the "Sumproduct" function.

i.e =SUMPRODUCT(((RawData!$C$2:$C$20000=$B$2)*(RawData!$K$2:$K$20000=D$1)*(RawData!$G$2:$G$20000=$C2)),RawData!$P$2:$P$20000)

basically it searches through the records to find entries that match the criteria.

D$1 references a cell with a date in it, in the UK format, dd/mm/yyyy.

the data pasted into the template is always UK format (dd/mm/yyyy).


i need them to always stay in dd/mm/yyyy format regardless of what the user has there region set to.

On Mon, 15 Aug 2005 07:32:57 -0500, jarvo
<jarvo.1tt42d_1124111115.9205@excelforum-nospam.com> wrote:

>
>
>i need them to always stay in dd/mm/yyyy format regardless of what the
>user has there region set to.

What do you mean by "them" ?

If you are referring to the dates in RawData, then you will have to ensure that
they are imported as TEXT and not as DATES.


--ron

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

On Tue, 16 Aug 2005 04:24:22 -0500, jarvo
<jarvo.1tuqec_1124186710.2413@excelforum-nospam.com> wrote:

>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

Ron,

thanks for the very long and informative post

i think that you are right and some of the fields in the "RawData" are text fields and not true date fields. i didnt notice it before as it all appears correct to the ***** eye.
my mistake.

so if all the date fields are actually serial numbers "38553" and this doesnt change, only the way the are represented on screen.
Therefore any calculations will look at the serial in the "Rawdata" and compare it the another serial.

so whether the dates are in US or UK format will not matter.