[libreoffice-users] Re: Date will not format or sort when imported into calc (ods)

No, the problem was importing date and time, date, time, and number and
having it formatted so the formatting may be modified. I learned three
things from the previous replies that I find very useful in resolving the
format problem. Each seems to trick the spreadsheet into accepting input as
if it were from a keyboard.

I was unaware of the new feature in the 'input data' dialog box, 'Detect
special numbers' and the ability to format the text before it is pasted into
the document. Paste special would usually come up with a dialog box that
would limit the formats to be pasted into the document, but when copied from
a text editor, it would show the 'unformatted' option as the only paste
option. In the latter case, I get the correct formatting if I remember to
format the column before pasting the date. This last option is available
when dealing with single columns. I sort that dates in the original
spreadsheet by time zone. Format the main spreadsheet with the template for
the time zone (calculates the relative offsets for all the other time
zones). Copy the date and paste it into the appropriate time zone column.
Copy and paste the rest of the data. Repeat for each time zone. This works
because both spreadsheets have the same formatting.

I receive dates in three different formats; big endian [31 Dec 2012], mixed
endian [Dec 31, 2012], and small endian [2012-12-31] (also known as
ISO8601). I prefer the ISO8601 format for date and time as I have been using
it before there was an ISO8601. On the rare occasion I get dates with two
digit years, I always presume 19nn because I presume the Y2k publicity would
have caused people to think of the year as a four digit number. I also
presume that anyone that would still use a two digit year for 20nn does not
think. I prefer not to deal with non-thinkers.

I receive times in two different formats; twelve hour clock [11PM OR
11:00PM] and twenty-four hour clock [23:00]. If the AM or PM is missing, I
presume the twenty-four hour clock format.

Time zones are usually not present, so I presume the time zone of the sender
or location of the data. When present, most time zones are defined as
offsets to UTC [Z±hh]. Those that are not are presumed to be the time zone
in effect on the posted date. For example, Eastern Daylight Time, EDT is
Z-04 and Eastern Standard Time, EST is Z-05, but Eastern Time, ET changes
depending upon the transition date and time. For example:
Sun 2012-11-04 02:00 Z-04 [EDT] becomes Sun 2012-11-04 01:00 Z-05 [EST]
Sun 2013-03-10 02:00 Z-05 [EST] becomes Sun 2013-03-10 03:00 Z-04 [EDT]

I wish there was a way to incorporate the time zone in the date and time
format, but since there is not, the work around is to include an extra
column with the time zone for each entry (sortable if in UTC offset format)
or separate columns labeled for the time zone of the entry (sortable in any
of the time zone columns). I have a column for each time zone I use and use
a template of formula to convert the entry column to correctly fill the
other columns. For example, Z±00 (UTC) entry in column A to Z-05 (EST/CDT)
in column F [=An-(5/24)] (where n is the row number). Column G Z-06
(CST/MDT) [=An-(6/24)]. Etc.

I have never used a database, so I do not know if what I am doing in the
spreadsheet is possible in a database. I can format the spreadsheet display
to split the screen so as to show the preferred time zone and the headers
and the data. I may compare the line items for specific dates visually or by
using a function in an unused column. If appropriate, I may generate a graph
of a range of data.



--
View this message in context: 
http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4006168.html
Sent from the Users mailing list archive at Nabble.com.

-- 
For unsubscribe instructions e-mail to: users+help@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