![]() ![]() LibreOffice needs to know the “format” of a cell. For instance, it’s pretty challenging to do numerical calculations with text (e.g., “apple” + 65 = ?). This is important because LibreOffice will treat cells with different formats differently. Cells can be formatted as numbers, as text, as dates, etc. It’s important to make clear at this point that LibreOffice Calc, just like other spreadsheet software, has different “formats” for cells. ![]() To illustrate this, I created a spreadsheet and inserted a bunch of dates: The issue with dates has to do with the format of the cells. Then come back here to see how this works with dates. So, if you want to learn how to use VLookup, first go to that previous post. It turns out, it does, but… There is a slight tweak required to make it work. Someone commented on that post and indicated that it didn’t work with dates. With this number format code you can represent any number as a time difference.In a different post on this blog, I showed how to use Vlookup to match lists. An easy example would be to use :MM:SS (am/pm makes no sense for time differences) which tells the number formatter that you don't want to calcuate times modulo 24 hours. In your case you want to show negative time (or time differences) so you want to represent negative numbers like -0.1 as -02:24:00 which requires you to adapt the number format code. The number format code that corresponds to 12:00:00 AM is HH:MM::SS AM/PM which says that the time should be calculated modulo 24 hours and AM/PM applied automatically. ![]() The second important thing to understand is that the number format code tells you how your value is interpreted. Now if you add the default time format you'll get something like 12:00:00 AM (for an en-US locale, for other locates the representation looks different). The value 1 represents 24 hours when formatted as time or one day past the zero date (can be changed in the options). The important part for working with time values as well as dates in LibreOffice (same is true for Excel) is to understand that these are just normal numbers with a special number format. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |