Andreas Säger wrote: *Because* spreadsheets are so flexible you can not edit databases with them.
Simply because all you have is a database excerpt in plain text. You never asked for flexibility. You asked for data integrity (keep the exact same date encoding when saving back to a database file). You do not want to use *flexibility*. What you ask for is automatic formatting on import. Yes, the Base component helps to import database data into preformatted spreadsheets and Writer documents. Base is *not* a database program. First and foremost it is a bridge to import various types of tabular data into preformatted documents. Calc can do all this without very easily with the help of the Base component which you strictly reject. What you asked for in the first posting was about importing the correct values rather than text and then you want the application to derive the correct number format code for each cell. This does not happen. Not in Calc nor Excel nor any other spreadsheet. Then you mentioned some co-editors. If they do not have have spreadsheet software at hand, what is the software they use? If you exchange data via csv, I would assume that they use some kind of database. Otherwise you could exchange spreadsheets in plain old xls format. Reply: I apologize that I was not clear in the fact that I needed a method of importing date and time data into a spreadsheet so that the date and time would be a date and time, not text, so I could present the date and time data according to the appropriate time zone by applying the appropriate formula to the input date and time data. The early replies supplied the information that cured my ignorance. I now have many different ways to convert text formatted date and time data into something I can work with in a spreadsheet. I also have several other options available that I have yet to try. The wealth of information presented is impressive. Once that problem was solved, I was presented with the idea of using a database or base component (I am still unclear as to the distinction). I explored the options and warnings for Libre Base and found no advantage and many disadvantages to using another method to enter, modify, calculate the results, and display the data. The reason is that once the date and time data is properly imported, the only process used is to apply the time zone's UTC offset. For this I add a column if the time zone is not already in use. In this way I may conveniently present any date and time data correctly for any time zone in use. I can then use split or freeze to keep the appropriate time zone column in place while scrolling through the rest of the data. Much of what I do is ephemeral, so I keep it as simple as possible. For example, a travel itinerary that spans time zones. A column for each time zone, a few columns for travel data, and a couple of columns for calculated incremental duration and total duration. The key feature is that the date and time data are available in each of the time zones. This also is a benefit when the traveler wishes to share the itinerary with others. The time zones of the others can also be incorporated at any time. If I were to do this type of setup in a database, would I be requried to set up a separate input field for each time zone with all the other time zones calculated? In order to make database, would I need to include all of the one hundred and five possible time zones (Z-12 to Z+14 in quarter hour increments)? This feature would have benefited the traveler in Jules Verne's story 'Le tour du monde en quatre-vingts jours' (1873). If the trip log had kept to the origin's time zone as well as the current local time, there would have been no problem as the traveler would have known the origin's date and time at all times during the trip. I do use programs that utilize a database. Firefox uses a database for the information it collects. Calibre puts the input data into a database. I do not "strictly reject" using a database when appropriate. I do have one set of data that may be amenable to a database. The input data has five columns; date and time, observed measurement, data discriminator, historical applicator used count/future data point, and historical dose applied with notes/future test units available with notes. I am considering spliting off the notes that have been inserted in the fifth input column and creating a sixth input column for notes, but have not arrived at a decision. Currently I slightly favor leaving it as is as the additional notes are few. The first four columns do have a fixed format, but the last two or three columns have a variable format that may change. The calculation columns are much more complex as I have not integrated all the calculations required into a single formula, but have additional calculations done with derived results to achieve the desired result. I currently have twenty-seven calculation columns that display interim results for each of the six data discriminator types and for the unfiltered data. I found lots of information about Libre Base and when I have assimilated it, I may try to set up a database for the test results. Currently there are 1554 rows of historical data, but as time progresses, the data count will increase. The rows of future data are projections based upon the historical data and are replaced with historical data when the observed measurement is entered. Please note that the data is only current from the time it is collected to the time the spreadsheet finishes its calculation update after the data is entered. A final point is that there are no co-editors. Exploring the option of a database, I visualized the primary reason I would use a database. I concluded that a database might be preferable if there were other people inputting data as that would standardize the data input into the database. -- View this message in context: http://nabble.documentfoundation.org/Date-will-not-format-or-sort-when-imported-into-calc-ods-tp4004907p4007326.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
