LibreOffice Calc – VLookup with dates

In a different post on this blog, I showed how to use Vlookup to match lists. Someone commented on that post and indicated that it didn’t work with dates. It turns out, it does, but… There is a slight tweak required to make it work. So, if you want to learn how to use VLookup, first go to that previous post. Then come back here to see how this works with dates.

The issue with dates has to do with the format of the cells. To illustrate this, I created a spreadsheet and inserted a bunch of dates:

It’s important to make clear at this point that LibreOffice Calc, just like other spreadsheet software, has different “formats” for cells. Cells can be formatted as numbers, as text, as dates, etc. This is important because LibreOffice will treat cells with different formats differently. For instance, it’s pretty challenging to do numerical calculations with text (e.g., “apple” + 65 = ?). LibreOffice needs to know the “format” of a cell. That is the key to making vlookup work with dates.

To check the format of a cell, all you need to do is right-click on it and select “Format Cells” (or Format -> Cells from the menus at the top of the screen):

Obviously, if you’re working with dates, then the format for the cells should be “Date” and whatever specific date format you want:

Okay, back to vlookup. To find a date in a list of dates, the value you are searching for also has to be a date in the Date format – ideally, they should be formatted identically. You can see in this spreadsheet, I inserted a date to search for in my list of dates:

The vlookup formula isn’t any different. In the cell just to the right of where I inserted the date I want to search for, I added my vlookup formula. Here’s what I put into my formula:

=VLOOKUP(E1,A1:A22,1,0)

This formula calls the “vlookup” function. “E1” is the date for which we are going to search in the list of dates. “A1:A22” is the list of dates in which we are searching. The “1” after that says to use the first item in the list of items we are searching for (kind of a weird requirement). And the “0” tells LibreOffice Calc that the list is not alphabetized or sorted.

Once I put that formula in, LibreOffice Calc will search through the list to see if my date is in the list. I know that my date is there. And, typically, vlookup will return the matching date if it is there, so it should return “8/27/2021”. But, here’s what I get when I hit return on my formula:

Rather than getting the target date, I get a weird number: 44435. Knowing a little bit about LibreOffice Calc, I quickly realized what the problem is: LibreOffice Calc doesn’t know that I’m searching for a date. It found the date, but the field where it is returning the value it found is currently formatted as a “Number,” not a “Date.” See:

To show that it found the matching date, the vlookup cell also needs to be formatted as a Date. So, change the formatting for the vlookup cell:

And once you hit OK, you should now see the date it found:

In short, yes, vlookup works with Dates. You just have to make sure that the cells where the vlookup formula is located are formatted as Dates not as Numbers and you’ll see which Dates match.

 565 total views,  4 views today

LibreOffice – Impress Templates and Object Styles

I just upgraded one of my computers to Kubuntu 18.10. With that upgrade came the latest version of LibreOffice (6.1.2.1).


With this new version, when I try to create a new Impress document, I’m now forced to pick a template. The options aren’t terrible, but I am of the opinion that presentations should focus on the content, not the pretty colors, shapes, or designs around the content.

I wanted a plain as can be, black and white template. There wasn’t one to be found. So, I created one.

I’m not going to go into the process of how to create a template in this post, but I do want to note one thing that I learned while creating my template. There is a quick way to change the characteristics of drawing objects in LibreOffice as well. I have always hated the default color when I create a box or circle in Impress (usually it’s some ugly blue color). It is possible to change the defaults for those, but doing so also seems to affect the defaults for outline boxes (not sure why). But it’s also possible to simply create a new style for objects and then apply that style once you’ve created your object, just like can be done with text. Here’s how you do that.

First, make sure you have your right side panel open in Impress and click on the Styles tab.

I initially tried changing the Default option in the list of styles, but that ended up changing the characteristics of the outline boxes, which I didn’t want to do. So, instead, what you need to do is create a new style (or modify one of the existing ones). You can create a new style by right-clicking on one of the existing styles and selecting “New” (my mouse was on “Modify” in the screenshot below). 

In the window that pops up, you can change the fill color under the “Area” tab and the line color under the “Line” tab. 

Since I like super plain, I changed mine to a grey fill with a black outline. Now, when I create a new drawing object, I can simply select it and double-click the style and it is how I like it:

I also saved this as part of my template. So, now, when I create a new Impress presentation, I load my template, and all of the settings I like are already in place.

(NOTE: How to save this as a template. Click on “File -> Templates -> Save as Template.” You’ll get a new window. Name your template and pick your folder. It will then be saved as a template for you that you can use when you create a new Impress file.)

 2,031 total views,  1 views today