| View previous topic :: View next topic |
| Author |
Message |
Maniac
General User
Joined: 22 Jan 2011
Posts: 7
|
 Posted: Wed Feb 09, 2011 10:24 am Post subject: (Solved) Changing US Date to UK Date Accurately |
 |
|
Hi,
I have a spreadsheet which has all of the dates automatically setup in the US format of MM/DD/YY, whereas I am in the UK so I would prefer to have them in the DD/MM/YY format.
The problem is, my settings are automatically set to UK, so even though the Date is in the US Style, I cant seem to find a way to switch then so that they convert to UK format...and when I do, it only changes the obvious ones 4/13/10 to 13/4/10 but wont change 4/12/10 to 12/4/10 as it seems to already think that this is the 4th of December instead of 12th April, so it really messes up the whole date list...
Last edited by Maniac on Wed Feb 09, 2011 4:54 pm; edited 1 time in total |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Feb 09, 2011 10:31 am Post subject: |
|
|
Test your dates with =MONTH(A1) if 12/10/99 is Oktober or December. If the result is #VALUE then it is text (a sequence of digits and slashes without value).
How did you get these data into your sheet? You did not type them, did you?
Which version of OOo do you use? See menu:Help>About...
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Maniac
General User
Joined: 22 Jan 2011
Posts: 7
|
| Posted: Wed Feb 09, 2011 10:50 am Post subject: |
|
|
Am currently using Version 3.2.1
It wasnt a sheet that I made myself, it is one that I have downloaded as it contained all the information I needed, I am just trying to go through it and sort through it to suit my own needs and keep having issues with the date.
So far I have been adjusting them by hand, which will take a while considering there are more than 27000 records to change so was hoping there was some way to do it automatically... |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Feb 09, 2011 11:03 am Post subject: |
|
|
Well, the information you need is probably not in the cells. Many users dump all sorts of tables into any kind of spreadsheet program cluelessly where plain text would be more appropriate.
There is no way to help you converting wrong data into right ones without knowing exactly what is wrong.
If you do not know the most fundamental basics we can not discuss anything here.
Most fundamental in this context:
The difference between a text and a formatted number.
The difference between a value and a formatting attribute.
How to apply formulas to a column of cells (relative addressing).
When you turn on menu:View>Highlight Values [Ctrl+F8] you will see numbers in blue font and text in black.
I would assume that dates with month numbers >12 are text and the numeric ones have day/month switched.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Wed Feb 09, 2011 12:41 pm Post subject: |
|
|
Three US dates that had been entered into UK context:
| Code: | 12/01/99
12/13/99
01/02/99 |
The first was meant to be 1st of December but actually it is 12th of January (=MONTH(A1) returns 1)
The left aligned value is text.
The third one was meant to be 2nd of January but actually it is 1st of February (=MONTH(A3) returns 2)
Adjust the application locale:
menu:Tools>Options...LanguageSettings>Locale: English(US)
Now you see
| Code: | 01/12/99
12/13/99
02/01/99 |
These values are exactly the same. The wrong numbers are displayed the other way round. They still represent 12th of January in A1 and 1st of February in A3.
The text remains just text but now the text should have a leading apostrophe visible in the formula bar. This apostrophe indicates that the formula expression would be a number.
Function VALUE(A2) converts the text in A2 to number (if possible) and I assume that 12/13/99 is meant to be 13th of December.
=DATE(YEAR(A1);DAY(A1);MONTH(A1)) returns the date in the same year with month and day switched. This fails if A1 is text.
Having our dates and text values in column A, starting at A1 and X being some unused column,
| Code: | | X1 =IF( ISTEXT(A1) ; VALUE(A1) ; DATE(YEAR(A1);DAY(A1);MONTH(A1))) |
converts a text in A to number and converts any number to another date.
Copy X1, select from X2 down until the end of the list and paste.
[Well, you may start at some other row since you did some of the conversion manually]
Dont be bothered when you see integer numbers. They are the correct unformatted day numbers. Format to some date format and see if things are correct.
Copy the converted X values and paste-special (Ctrl+Shift+V) over the original values. Check "Numbers", "Dates" and uncheck "Formulas" so you paste the formula results without the formulas.
All this may fail if my initial assumptions about your data are false. But you work with backup copies, don't you?
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Maniac
General User
Joined: 22 Jan 2011
Posts: 7
|
| Posted: Wed Feb 09, 2011 5:17 pm Post subject: |
|
|
Cheers, this seemed to do the trick, although I did eventually figure out another way.
Since the data came from a CSV file I managed to start again and seperate the date using the / when opening the CSV initially, and then it was a case of copy/pasting the "Day" column to put it before the "Month" column and used the CONCATENATE function to bring them back together into a date form again...
This did mean starting from scratch again, but I had a couple of other reasons to start again from scratch anyway so it hasnt cost me too much time luckily... |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Thu Feb 10, 2011 1:32 am Post subject: |
|
|
This is why I asked about the origin of data. In the csv import dialog you can select column(s) in the preview and specifiy what they are supposed to be: YMD, DMY or MDY, text or US Engl.
You may also switch the application locale to US, do the import and then switch back once you have all values right.
In the import dialog of version 3.3 you can set the assumed locale for the entire import in the dialog and the dialog preserves its settings.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
|
