Canadian to US date format

[macAlter]

Working with dates that I imported. They're set as Canadian = DD/MM/YYYY (01/12/2014 but I'd like them to be American MM/DD/YYYY (12/01/2014. Is this possible?

[acknak]

If the dates are imported as date values, you can display them any way you like.

Select the cells, then Format > Cells > Number > Category: Date ...

If changing the format has no effect, then your dates are probably stored as text. You'll have to convert them to numeric values before the format will take effect.

[macAlter]

If the dates are imported as date values, you can display them any way you like.

Select the cells, then Format > Cells > Number > Category: Date ...

If changing the format has no effect, then your dates are probably stored as text. You'll have to convert them to numeric values before the format will take effect.

I think my date problem is in FileMaker where I'm importing the data to. It's formatting in Calc set to Date (thought I had done that). 01/12/2013 Calc is reading Jan 12 2013 in FileMaker, not Dec 01 2013. So will try and find what happened with FileMaker. Thanks.

[Villeroy]

FileMaker is a database. You've got to understand the facts about data types in databases and in spreadsheets. The text "1/1/2013" is NOT a date regardless of any number format. Formatting has zero influence on your actual data. If any date strings do work in the context of database import, this would be unambiguous ISO dates such as "2013-01-01"

[macAlter]

FileMaker is a database. You've got to understand the facts about data types in databases and in spreadsheets. The text "1/1/2013" is NOT a date regardless of any number format. Formatting has zero influence on your actual data. If any date strings do work in the context of database import, this would be unambiguous ISO dates such as "2013-01-01"

I thought Tools > Text to Column > Date would convert the dates. Obviously not. The raw data isn't in ISO format, Canadian standard is DD/MM/YYYY.

[Villeroy]

Why don't you import the dates correctly? This would be much easier to do than fixing wrongly imported dates.
With text-to-columns "2/1/2013" becomes either 2nd of January or 1st of February whereas "1/31/2013" becomes 31st of January or it remains text because there is no 31st month. When you play around with locale settings and text-to-column you may end up with a mess of correct dates and wrong dates.
Importing dates from text files or from clipboard requires that you simply do not ignore the import options. You specify the import language (US English if there are dates like 1/31/2013, UK English for dates like 31/1/2013) and you check the "Special Numbers" option. That's all. In rare cases you may want to specify the import type for some column separately which can be done in the preview table of the import dialog. That dialog has also a [Help] button leading you to a well written help page.

Contrary to the subject line "Canadian to US date format" this is not about formatting. It is about correct data vs. wrong data. Correct data can be formatted easily any way you want.

[macAlter]

Why don't you import the dates correctly? This would be much easier to do than fixing wrongly imported dates.
With text-to-columns "2/1/2013" becomes either 2nd of January or 1st of February whereas "1/31/2013" becomes 31st of January or it remains text because there is no 31st month. When you play around with locale settings and text-to-column you may end up with a mess of correct dates and wrong dates.
Importing dates from text files or from clipboard requires that you simply do not ignore the import options. You specify the import language (US English if there are dates like 1/31/2013, UK English for dates like 31/1/2013) and you check the "Special Numbers" option. That's all. In rare cases you may want to specify the import type for some column separately which can be done in the preview table of the import dialog. That dialog has also a [Help] button leading you to a well written help page.

Contrary to the subject line "Canadian to US date format" this is not about formatting. It is about correct data vs. wrong data. Correct data can be formatted easily any way you want.

Sorry, not sure I understand your comment "Why don't you import the dates correctly?". I'm not importing into OO. I'm trying to import the columns into FileMakerPro. FPro can read *.xls files. However, FileMaker Pro is reading 2/1/2013 as Feb 1, 2013 even if I change my OS date format so am trying to contact them.

[Villeroy]

Working with dates that I imported.

I'm not importing into OO.

How do these values get into your sheet?
Are they numbers, text or a mixture of dates and text? [hint: Ctrl+F8 highlights numbers with blue font]

[macAlter]

Working with dates that I imported.

I'm not importing into OO.

How do these values get into your sheet?
Are they numbers, text or a mixture of dates and text? [hint: Ctrl+F8 highlights numbers with blue font]

The raw data was an HTML table online. I highlighted and pasted into Calc columns. One column is START TIME showing as 04/01/2012 4:00 PM which was date and time combined so I used the formula to separate =LEFTB(B2;10) which read the 04/01/2013 portion and placed in new column. I then copied the entire column and did Paste Special into empty column to strip out the formula. I highlighted the column and did DATA > TEXT TO COLUMNS > and changed Standard to DATE DMY. When highlighted shows 2012-01-04 (I did not set to use hyphens) in the formula bar and 04 Jan 2012 in the column.

I then used formula in another column to read the time and used similar process.This worked okay.


PS: Ctrl+F8 must be a Windows option as it doesn't work on Mac.

[Villeroy]

Do not paste html because html is not about raw data. Use menu:Edit>PasteSpecial [Cmd+Shift+V] and import "unformatted text". The next dialog has a language selector. This option is about the meaning of incoming data. It is not about your personal preference.
If "04/01/2012" refers to the first of April 2012, then you choose "English (US)", otherwise you choose "English(UK)" which will import a date that refers to the 4th of January. All the other languages are useful with other flavours of dates such as German "1.4.2012". With the correct language option, Calc can even interprete dates with long and short month names (Oct, October). Check the "Special Numbers" option too.
Once you've imported correct numeric data, you are free to display the numbers in any number format you want. But number formats never change any data. For instance, you may have imported "04/01/2012" as a US date and the respective cell displays 01/04/12 because your spreadsheet uses a UK or Canadian locale. This is perfectly fine. The copied text refered to the first of January and this is the date that has been imported into your Canadian spreadsheet where the same date reads "01/04/12".
TEST: =MONTH(A1) returns the month number (1 to 12) of the numeric value in A1.

Ctrl+F8 might be Cmd+F8 on the Mac or menu:View>Highlight Values.

[macAlter]

Villeroy: Just cleaning up existing stuff.. CMD+F8 on Mac is correct. I never heard of this feature. Checked Help and got the colour codes. Neat. Will l do the test on next round to "import" as you explain. I know HTML isn't the best raw source but obstacles would be easier to handle if the developer had kept date and time as two fields. Was offered an Excel version so will get that to see if in fact they did combine as a text field.

[macAlter]

Okay, what's happening is painful. I had to change my OS date preferences. Heaven knows what's been affected! I then matched OO's format of YYYY-MM-DD to FileMaker (I *never* have used dashes before). That let's me do copy into FPro. However, it means I can't revert back to my original OS date preferences to meet criteria of most applications I use. (Most don't recognize ISO) and my website will be affected which means I have to revert.

dashes.png (12.46 KiB) Viewed 5322 times

May have to abandon this whole thing and do manual labour.

[Villeroy]

No, you do not have to change your OS preferences. You can change the OpenOffice preferences under "Language Settings">Languages>Locale (option #2). The locale defines how all numerals in the whole office suite are interpreted by default.
This default can be overridden in the text import dialog, in cell styles, in cell format dialogs (Calc and Writer), in numeric Writer fields, most database form controls etc.
The formula bar in your screenshot indicates that you imported/entered 4th of January 2012. The ISO date is unambiguous. The displayed cell value could also mean 1st of April. This can be formatted with another number format locale (Format>Cells... [Numbers] Language) just like you can change the colors and the font of the cell. The value remains the same.

Instead of changing the number format locale after the import, you can paste unformatted text with Canadian DMY dates into cells that are preformatted as US American cells (e.g. using a prepared spreadsheet template with US cell styles) and the following text strings imported with "Special Numbers" option as English (Canada or UK) data ...

Code: Select all

22/3/13
19/6/12
13/12/11

... are displayed as ...

Code: Select all

03/02/13
06/01/12
12/13/11

.. which are the correct dates displayed with another locale.
I got this result with a German office locale, overriding the import locale (Canadian) and the target cells locale (USA).

Quick customized US spreadsheet template
Open a new spreadsheet.
Call the stylist window (F11)
Right-click "Default">Modify...
Tab: [Numbers]
Language: English(US)
File>Templates>Save... give some name, say "US Spreadsheet"

New US spreadsheet:
File>New>From Template... (Cmd+Shift+N)

If you want all new spreadsheets to be in US locale by default:
File>Templates>Organize...
Pick your spreadsheet
[Command Button] > Set Default

[macAlter]

I tried to paste special the data using the various date options for importing. I tried your suggested Template but that too doesn't help because it's not US format I require, it's Canadian. the image hopefully explains my attempts. The raw data includes a "time component" which is stripped out as it's not required.

My Preferences are set as:
User interface: Default - English USA
Locale setting: Default - English (Canada)
Default currency: Default (CAD)
Default language for docs: Western - English (USA)

When I changed the Locale to English USA for a Template, it changed the separator from "-" to "/" but then read the date incorrectly as MMDDYYYY, not DDMMYYYY. In the format Number window, it shows openOffice uses dashes for Canadian dates. I need Canadian dates with slashes: 01/04/2012 to be April 01, 2012 not 01-04-2012 reading as April 01, 2012. This is what prevents me from either importing into FileMaker or copy/paste in.

These are different options for Paste Special when importing. I wanted to see for myself how each option would handle the information.

[RoryOF]

Try Locale English(GB) for dates with slashes.

[macAlter]

Try Locale English(GB) for dates with slashes.

When I tried that, the spreadsheet changed the date 2012/Jan/04 which is correct. The address bar read 04/01/2012 which means I'll need to consult a FileMaker user group to determine the misreading of the date. It displayed as 01 April 2012. Incorrect (04/01/2012 where format was set to read 01 as the month). That said though, UK format did give me the "/" I required. I'm building up ammunition to have the source information redone!

[macAlter]

Do not paste html because html is not about raw data. Use menu:Edit>PasteSpecial [Cmd+Shift+V] and import "unformatted text". The next dialog has a language selector. This option is about the meaning of incoming data. It is not about your personal preference.
If "04/01/2012" refers to the first of April 2012, then you choose "English (US)", otherwise you choose "English(UK)" which will import a date that refers to the 4th of January. All the other languages are useful with other flavours of dates such as German "1.4.2012". With the correct language option, Calc can even interprete dates with long and short month names (Oct, October). Check the "Special Numbers" option too.
Once you've imported correct numeric data, you are free to display the numbers in any number format you want. But number formats never change any data. For instance, you may have imported "04/01/2012" as a US date and the respective cell displays 01/04/12 because your spreadsheet uses a UK or Canadian locale. This is perfectly fine. The copied text refered to the first of January and this is the date that has been imported into your Canadian spreadsheet where the same date reads "01/04/12".
TEST: =MONTH(A1) returns the month number (1 to 12) of the numeric value in A1.

Ctrl+F8 might be Cmd+F8 on the Mac or menu:View>Highlight Values.

I went back and followed all your steps using Paste Special. Import did not work as expected ("/"). I got "12-01-04" when setting Language to Default English UK; Detect special numbers; Separator Fixed width (to separate the date/time into two columns); Fields: changed Standard -> Date DMY. (Jan 04, 2012). Not sure why I got 2 digit year field, the raw date used 4.

[Villeroy]

12-01-04 is the correct value, isn't it? Importing correct values is the one and only concern. The rest is about cosmetics. Why don't you format the cells? Why don't you paste special into pre-formatted cells of a template?