Home |
Search |
Today's Posts |
#1
|
|||
|
|||
keeping dates in UK format
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 |
#2
|
|||
|
|||
On Mon, 15 Aug 2005 04:11:57 -0500, 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. 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 |
#3
|
|||
|
|||
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 CellsNumberCustom. 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 |
#4
|
|||
|
|||
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)),R awData!$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. -- jarvo ------------------------------------------------------------------------ jarvo's Profile: http://www.excelforum.com/member.php...o&userid=18983 View this thread: http://www.excelforum.com/showthread...hreadid=395748 |
#5
|
|||
|
|||
On Mon, 15 Aug 2005 07:32:57 -0500, jarvo
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 |
#6
|
|||
|
|||
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 :( -- jarvo ------------------------------------------------------------------------ jarvo's Profile: http://www.excelforum.com/member.php...o&userid=18983 View this thread: http://www.excelforum.com/showthread...hreadid=395748 |
#7
|
|||
|
|||
On Tue, 16 Aug 2005 04:24:22 -0500, jarvo
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 |
#8
|
|||
|
|||
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 naked 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. -- jarvo ------------------------------------------------------------------------ jarvo's Profile: http://www.excelforum.com/member.php...o&userid=18983 View this thread: http://www.excelforum.com/showthread...hreadid=395748 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
How do I convert dates to text keeping the format? | Excel Discussion (Misc queries) | |||
the dates on cell format make different dates. | New Users to Excel | |||
Can I change a column of dates from USA to UK format? | Excel Discussion (Misc queries) | |||
Any way to force European format dates in sheets? | New Users to Excel |