Ask Your Question
0

Calc: recognize MM/YYYY as date

asked 2020-10-16 05:25:54 +0200

DanaRattle gravatar image

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?

edit retag flag offensive close merge delete

Comments

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

Opaque gravatar imageOpaque ( 2020-10-16 11:40:22 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2020-10-16 15:59:04 +0200

erAck gravatar image

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).

edit flag offensive delete link more

Comments

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

DanaRattle gravatar imageDanaRattle ( 2020-10-16 17:53:48 +0200 )edit
0

answered 2020-10-16 13:03:34 +0200

Opaque gravatar image

updated 2020-10-16 13:06:05 +0200

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.

edit flag offensive delete link more

Comments

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.

DanaRattle gravatar imageDanaRattle ( 2020-10-16 17:48:10 +0200 )edit
0

answered 2020-10-16 07:17:31 +0200

Zizi64 gravatar image

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?

edit flag offensive delete link more

Comments

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.

DanaRattle gravatar imageDanaRattle ( 2020-10-16 18:02:01 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-10-16 05:25:54 +0200

Seen: 36 times

Last updated: Oct 16