Managing date formats in calc (changed the question to add "date")

[moonvine]

Each month the bank holding my credit card account provides a downloadable statement in CSV format. Each month I download the CSV file and covert it to a spreadsheet format using CALC. It quarter I consolidate the monthly files using “copy and paste” The worked well until July 2014. The dates in the bank file were in MM/DD/YYYY format and they later switched to YYYY/MM/DD format. Using CALC in Libre Office 4.4 (beta) to move from CSV to spreadsheet format I managed to convert the dates to MM/DD/YYYY and the year displayed as 2014. Eg., 11/15/2014. However, when performed the quarter consolidation the dates suddenly displayed as 2018. Eg., 11/15/22018. I have been unable to resolve this issue. All default beginning dates are set to 12/30/1899.

There may be something obvious I should change but I can’t find it. Help will be appreciated. I’m using a fresh copy of Win 8.1 home if that has some bearing on the problem. My computer is a new Samsung all in one.

[Lupp]

Why don’t you accept the format the bank switched to? It’s a good choice.

A detaild help in the case might be difficult: There may be more than one specifics in the processes of import, converting/transforming and consolidating playing their role in producing the strange result.

[rautamiekka]

YYYY-MM-DD is the correct way to represent a full year, both from administration and usability point. That’s the format of our computers, yet LO doesn’t use it, so you have to set the default somewhere … I’m looking into it.

[rautamiekka]

AFAIS, the only ways are to use a specific locale (extremely stupid) and make a template file that you’ll always use. Stupid as something.

[Lupp]

@moonvine: I would suggest you edit the subject of your original post and replace “calc formats” by “date formats in Calc”. Others having a similar proble migth more likely find the thread.

[Lupp]

If you want to get the “dates-and-their-formats” problem actually solved:

1. Make sure you always import dates as numeric values. How this will reliably work may depend on details of the csv you get them from. (Let us see one row of such a csv [confidential data cut] to get more detailed advice.) Never enter dates via keyboerd explicitly as text.

2. Don’t work on dates by text (string) manipulation during/in advance of import.

3. 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. 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 “2014/11/15” 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. It will, unfortunately, also allow for “2-digit-years” but you won’t worry.

4. The date format actually used by Cells to display correctly represented date values is independent of the recognition patterns. The default depends on the locale but this is easily overridden by the setting under > ‘Format Cells…’ > ‘Numbers’ . The internationally most redable and most unambiguous format in conformity with ISO 8601 should be “YYYY-MM-DD”.

5. If a date displayed does not comply with the chosen number format of the cell it (wrongly) is still text and needs recognition. The proper tool should be > ‘Data’ > ‘Text to Columns…’ (despite its slightly misleading name). In special cases it may also be done by formulae using temporary helpers. More information needed for giving detailed advice.

6. Rectifying a recognised but wrongly calculated date value can be done by formulae in most cases. You need a strcictly applicable rule, of course. (“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. The results may be subsequently pasted ‘Special’, values only, over the wrong values.

[moonvine]

Lupp, thanks for your suggestions. I will try them later today or tomorrow. Also note that I changed the title for my question. It is more definitive now. I am including the date portion of the December file I downloaded. I have noted that each date is preceded by ’ so I suppose this formats the original file dates as text.

2014-11-26
2014-11-13
2014-12-03
2014-12-03
2014-11-07
2014-11-07
2014-11-07
2014-11-07
2014-11-08
2014-11-10
2014-11-10

[moonvine]

Lupp, its me again. The dates in my previous comment were in a column when I entered them. I don’t know how to get them in a column this purpose.

[Lupp]

@moonvine: Thank you for cooperation. However, the dates as you included them won’t help me much understanding. I would need an actual part from your csv containing some dates (as gottem with the help of a txt editor, e.g.) and, if possible, a reduced part of your Calc sheet to have a chance to find a reason for your strange experiences. You might edit your question to upload 2 little files for the purpose.

[moonvine]

I’ll see what I can do

[moonvine]

While trying to duplicate the situation that prompted my original question I discovered several conflicting scenarios between MS XCEL 97-2003 (used by my bank in 1Q 2014), Libre Office 4.x and Open Office 4.1.x. The bank was using tab delimiters in the download XCEL file and when I opened the download file the YYYY in the date changed from 2014 to 2010. Yes, I know this is different than earlier description of my issue. About midway through the year the bank switched to Open Office 1.1 for the CSV download file. In addition I upgraded my copy of Libre Office in this time frame. Using Libre Office 4.3 and 4.4 (beta) I now have the dates in the summary I have created in MM/DD/YYYY order. I’m using that format because it is what the bank started the year with.

As a final note, pay close attention to the download CSV file and the software you use. My thanks to everyone who offered suggestions and the benefit of their experience.

[Lupp]

I think you once had accidentally chosen the (odd and deprecated) option ‘1904-01-01’=1 as base for dates in > ‘Tools’ > ‘Options’ > ‘LibreOffice Calc’ > ‘Calculate’ > section ‘Date’. The recent installation of LibO 4.x changed it to (‘1899-12-31’=1) or to ‘1900-01-01’=1

This is an edited version of the original comment.