![]() I wanted to sort the data by date and it was a small data set so I managed rather tediously to convert by hand. AOO 4. I downloaded a csv file with dates in the following format Mon Jun 22 08:08: and couldn't work out an easy way of converting it to a date format. or CTRL+F3 select My autotext give a name and short cut (I suggest 2007) on Auto text button select 'New' Usage: write 2007 into a file and push F3, your field will be entered. The results may be subsequently pasted ‘Special’, values only, over the wrong values. Make new file, insert date into desired formatted field, select it. (“Always exactly 4 years less”, e.g.: =DATE(YEAR(D11) - 4 MONTH(D11) DAY(D11)) if the wrong date is placed in cell D11.) For this we will need a helper cell/column again, of course. You need a strcictly applicable rule, of course. ![]() Rectifying a recognised but wrongly calculated date value can be done by formulae in most cases. More information needed for giving detailed advice. In special cases it may also be done by formulae using temporary helpers. The proper tool should be > ‘Data’ > ‘Text to Columns…’ (despite its slightly misleading name). If a date displayed does not comply with the chosen number format of the cell it (wrongly) is still text and needs recognition. #OPENOFFICE CALC DATE FORMAT ISO#The internationally most redable and most unambiguous format in conformity with ISO 8601 should be “YYYY-MM-DD”. The default depends on the locale but this is easily overridden by the setting under > ‘Format Cells…’ > ‘Numbers’. The date format actually used by Cells to display correctly represented date values is independent of the recognition patterns. It will, unfortunately, also allow for “2-digit-years” but you won’t worry. If “5” was not recognised as the 15th day of november 2014, e.g., you may add there “ Y/M/D” (the semicolon only delimiting this pattern from preceding ones) and next time recognition will work. If a date formatted as a text entered or imported is not recognised by your LibO Calc you have to add a recognition pattern via ‘Tools’ > ‘Options’ > ‘Language Settings’ > ‘Languages’ > ‘Language of’ > ‘Date acceptance patterns’. If dates are entered (or read from a csv in most cases) they originally are strings, unfortunately, and need recognition by an automated process for conversion. (Let us see one row of such a csv to get more detailed advice.) Never enter dates via keyboerd explicitly as text.ĭon’t work on dates by text (string) manipulation during/in advance of import. How this will reliably work may depend on details of the csv you get them from. Make sure you always import dates as numeric values. If you want to get the “dates-and-their-formats” problem actually solved: ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |