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: [email protected] 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
