keeping dates in UK format

[jarvo]

hi,

Is it possible to stop excel changing the date format when it is opened in another region. i.e from UK to US.

i have created a template that is used by several people in different regions, it uses the UK date format for filtering records by date.

However when the template is opened in another region (ie USA) the dates change format, to the region (locale) set on the users computer.
This stops the template working as intended.

so i want the dates to stay in the UK format no matter where they are opened.

thanks.

[Ron Rosenfeld]

On Mon, 15 Aug 2005 04:11:57 -0500, jarvo
<[email protected]> wrote:

>
>hi,
>
>Is it possible to stop excel changing the date format when it is opened
>in another region. i.e from UK to US.
>
>i have created a template that is used by several people in different
>regions, it uses the UK date format for filtering records by date.
>
>However when the template is opened in another region (ie USA) the
>dates change format, to the region (locale) set on the users computer.
>This stops the template working as intended.
>
>so i want the dates to stay in the UK format no matter where they are
>opened.
>
>thanks.

You could store the dates as TEXT rather than as Excel dates.

Or you could use a different algorithm to filter your records.

Excel stores dates as serial numbers with 1=1/1/1900 or 1/2/1904 depending on
the date system in use. How it is displayed in the workbook does not affect
what has been previously stored in the cell. How it is parsed when entered
into the workbook is affected by the Windows regional settings.

Perhaps if you give more detail? ...


--ron

[Monte]

This has more to do with the Date Settings on the user's Regional Options
than your worksheet formats. However, you can counter this by customizing
the cell format using Format Cells>Number>Custom. I'm not sure what UK format
is but here are a few examples using "01 Jan 2005" you can work with...

dd = 01, ddd = Sat, dddd = Saturday
mm = 01, mmm = Jan, mmmm = January
yy = 05, yyyy = 2005

You can also display dividors by entering them in the format...
dd-mmm-yyyy returns 01-Jan-2005
dd-mmmm-yyyy returns 01-January-2005
ddd-dd-mmmm-yyyy returns Sat-01-January-2005



"jarvo" wrote:

hi,

Is it possible to stop excel changing the date format when it is opened
in another region. i.e from UK to US.

i have created a template that is used by several people in different
regions, it uses the UK date format for filtering records by date.

However when the template is opened in another region (ie USA) the
dates change format, to the region (locale) set on the users computer.
This stops the template working as intended.

so i want the dates to stay in the UK format no matter where they are
opened.

thanks.


--
jarvo
------------------------------------------------------------------------
jarvo's Profile:
http://www.excelforum.com/member.php...o&userid=18983
View this thread: http://www.excelforum.com/showthread...hreadid=395748

[jarvo]

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.

[Ron Rosenfeld]

On Mon, 15 Aug 2005 07:32:57 -0500, jarvo
<[email protected]> 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

[jarvo]

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

[Ron Rosenfeld]

On Tue, 16 Aug 2005 04:24:22 -0500, jarvo
<[email protected]> 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

[jarvo]

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.