Calc: recognize MM/YYYY as date

[DanaRattle]

I have an HTML document with a table in it. The table includes a date column with dates in MM/YYYY format.

I’m able to open the document in Calc and save it as .ods but whether or not I check the “Detect special numbers (such as dates)” box when importing it I cannot figure out how to make that column behave as a date.

I have formatted the column as Date and I have tried Text to Columns to specify a date format, but sorting by the column in ascending order still seems to treat it as text:

10/2001

10/2002

10/2003

11/2001

11/2002

11/2003

12/2001

etc.

I feel certain I must be overlooking something obvious. How do I get Calc to recognize these as MM/YYYY dates?

[anon73440385]

10/2001

How should this data(!) interpreted as a date? This is not a date (even when reading that text it doesn’t tell a date) since it doesn’t contain a day and there is no algorithm (without making assumption) to turn this into a number (which is what real calc dates are). 10/2001 seems to mean a period of 31 days in October 2001 which is a quite different thing than a date. If you want it to turn into a real date add a (fake) day (hint: may be using Edit -> Find & Replace ) and format using format code MM/YYYY

[erAck]

You can append ;M/Y to your Date acceptance patterns under Tools → Options → Language Settings → Languages, Formats. Then an input of 10/2001 will yield the date 2001-10-01 (whatever formatted to your locale’s default date format).

[DanaRattle]

Ahhhh, thank you! That works! I appreciate your help.

[Zizi64]

I have an HTML document with a table
in it. The table includes a date
column with dates in MM/YYYY format.

I’m able to open the document in Calc
and save it as .ods but whether or not
I check the “Detect special numbers
(such as dates)” box when importing it
I cannot figure out how to make that
column behave as a date.

The MM/YYYY is a localised format of the textual date values. The Calc must recognize the textual dates to convert them to numeric Date type.

Try to choose your locale mark in the Import Filter. If the locale of the dates and your locale settings match, then the Caly will recognize the teytual dates as numbers.
If this method not work with the whole document, then you can try to import the table separatelly (Copy - Paste Special)

For the future: Use the international standard (ISO 8601) date format: YYYY-MM-DD in your html documents. The LO always can recognize this format as a date.

Can you upload sample files here?

[DanaRattle]

Thank you; adding MM/YYYY to the Date Acceptance worked (as did the find/replace).

I wish the source document were under my control, but it’s generated from a US Treasury website for tracking and calculating savings bond values. It doesn’t seem like a very efficient format for this kind of information, but someone apparently thought it was a good idea.

[anon73440385]

Hello,

related to my comment (and assuming you are using an English (USA) locale)

• Select the column/range containing your data
• Edit -> Find and Replace
  Find: (..)/
  Replace: $1/01/ (adds the first day 01 as a fake day to your data)
  Other Options 1: [x] Current selection only
  Other Options 2: [x] Regular expressions
• Click Replace All
• Menu Format -> Cells > Tab: Numbers
• Select Category Date: Format Code MM/YYYY

Note: Do not format the data column/range in advance and use CTRL+M to remove any formatting you might have performed already.

[DanaRattle]

Thank you–this does the trick.

Excel offers a format option that recognizes MM/YYYY and converts an entry of 10/2003 to an actual date of 10/1/2003 but the find/replace in Calc will accomplish what I need. Much appreciated.