Re: [libreoffice-users] Re: Apostrophe prepended to MM/DD/YYYY

What I have learned:

Most important: Brian is very helpful. Thank you.

Dates are really floating point numbers and the unit is one day. 
Somewhere there must be a way of setting the zero point. 1900, 1904, 
1970 are common. My startup worksheet came from an old, 2001, Apple 
version of Excel and, though I have seen some time values in cy 2020, 
almost all are based on 1904 and show 2016 as the year. Time values 
appear to be rounded so that differences of less than a second get lost 
while sorting. They might be using a 32 bit float.

The format used by US Bank for it's delivery of data as *.csv files 
offers lines like this:

"8/30/2016","DEBIT","DEBIT PURCHASE -VISA OOMAINC 
xxx-xxx-xxxxCA","Download from usbank.com. OOMAINC xxx-xxx-xxxxCA","-4.2300"

All entries include quote marks and I can't tell if they ever escape 
quotes that are required inside of regions of text. The DEBIT entry is 
often a CREDIT instead. The "-" minus sign is redundant and just means a 
debit.

Getting the download requires use of a browser. I don't think 
Libre_Office has the required security and I don't have time to search 
that way but Firefox will save the bytes in a Linux file where there are 
no provisions for formatting. The calc module will open the file and 
senses the .CSV well enough to read into a spreadsheet format. It 
recognizes the MM/DD/YYYY format and converts the times to the internal 
format used by calc. It also handles the extra zeros in the US$ format. 
There is some white space in front of the xxx items and the xxx's are my 
replacements of decimal digits. I think calc is ignoring them - fine.

I find it convenient to introduce the downloaded information using a 
sheet in the workbook that handles everything else. with the downloaded 
spreadsheet open and the destination open in another screen I can copy a 
bunch of data into columns starting with J.

There are a bunch of details such as identifying column K to see if it 
is a check number and changing a format for that. No big deal and the 
Excel page is adequate except!!!!

Copying those MM/DD/YYYY dates into column A and B, where they are 
needed when the new data is entered into the big spreadsheet which 
handles a year of data, can be a pain. The format data for columns A and 
B must be set BEFORE the data is moved over and they don't seem to 
remember that. What works is to fill the destination columns with the 
formula =VALUE(Jx). Somehow that causes calc to recognize the MM/DD/YYYY 
format and convert the integers to an appropriate floating point 
date/time value. If you just use =Jx you get an apostrophe in front of 
the MM/DD item. That's an Excel concept that tells the software to leave 
the data alone. It's useful for specifying long numbers of more that 14 
digits that are found for the likes of part numbers or encoding keys.

The "Format / Number Format / Date" option under the Apple-like menu bar 
is interesting and will let you specify a date format for empty cells 
but it varies between showing a floating point number like 41146 and the 
equivalent 08/26/16. If you don't see what you want try it again.  You 
can add fractional parts to the first format like 41147.25 which moves 
it to 08/27/16 which is limited to the day part. Formatting it as time 
results in "AM".

On this Linux box I like to work with a shell which leads me to a simple 
command to open my spreadsheet. I also noticed the availability of an 
error file. This is a small part of the result. It's mostly warnings but 
those items 34, 36, and 38 are repeated well over 25000(10) times before 
I trimmed them down. I also have zero experience with Java. The machine 
uses Ubuntu and it's current.

Wed Oct 12 14:28:18 EDT 2016
Opening Finance_16.ods with libreoffice --calc
javaldx: Could not find a Java Runtime Environment!
Warning: failed to read path from javaldx
(soffice:27204): Gdk-WARNING **: gdk_window_set_icon_list: icons too large
(soffice:27204): Gdk-WARNING **: gdk_window_set_icon_list: icons too large
(soffice:27204): Gdk-WARNING **: gdk_window_set_icon_list: icons too large
** (soffice:27204): WARNING **: Unknown event notification 38
(soffice:27204): Gdk-WARNING **: gdk_window_set_icon_list: icons too large
** (soffice:27204): WARNING **: Unknown event notification 36
** (soffice:27204): WARNING **: Unknown event notification 34



--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted