How to Continue Numbering in Libreoffice Spreadsheet Templates

How to create a calendar template in LibreOffice Calc that can be updated dynamically with any year or month. The tutorial includes a calendar template(.ods).

In this post I will show you how to make an editable calendar template for LibreOffice Calc in which you can simply change the month or the year and the entire calendar gets updated.

To those who are only looking for a LibreOffice calendar template that they wish to print, simply download one of the .ods files below:

• LibreOffice Calendar Template US Letter
• LibreOffice Calendar Template A4

For more styles and designs check out these printable calendars (PDF).

Before we begin, allow me to show you the end result:

Let's start by making a new spreadsheet. From the menu select File -> New -> Spreadsheet.

Because we intend to make this template be printable we have to setup the printable area. In the menu go to Format -> Page…

In the "Page" tab choose the format of the paper. I went for US Letter, but you can choose another one, A4, for example, depending on which format is the most popular where you live. Next, select "Landscape" for orientation.

A little bit lower, you can set the margins. You can leave the default values but I opted for some more rounded values. See the following image:

Once you click OK you should see some dashed lines in your spreadsheet. Those define the print area. We'll come back to this later when we need to style the calendar.

Now let's rename the existing sheet in our spreadsheet to "Month Calendar". You can do that by double clicking on the tab "Sheet1". Additionally, create a new sheet called "Settings", similarly to what you see in the image below.

Considering this is a dynamic template, and to avoid typos, we should define the month names. LibreOffice doesn't have a list of month names, so we'll need to make our own. This also means you can have the month names in another language. I'll use English, but you can experiment with something else.

If you're not in the "Settings" sheet, switch to it. In the first column start writing the month names, one below the other.

Next, we'll use this list as a drop-down. For this, select the first cell(A1) in the "Month Calendar" sheet. Then from the menu choose Data -> Validity…

In the "Validity" panel, under the "Criteria" tab, select "Cell Range" from the options for the "Allow" section. Then in the "Source" field we are going to define the range. This will be $Settings.$A$1:$A$12 . Simple translation: get the cell range starting from A1 to A12 in the "Settings" sheet.

After clicking OK you should see a button with an arrow pointing down next to the first cell. Clicking on that button should reveal the drop-down list with all the months, like in the image below.

Although it complicates things, we'll have to start styling things. First, the month has to be larger. Choose a font you like and a size. I went with the default font, size 40. I also merged the top 3 cells to give the month more space.

Now, it's time for a bit of math. We need to calculate the width of a day, in our case, the width of a column. So given that there are 7 days in a week we'll use 7 columns for our calendar. This means we'll have to divide the width of our page to 7. Let's have a look back at our page settings. From the menu choose Format -> Page…

Open your calculator application. For me it's (11-2*0.75)/7=1.3571 . Note: only use the first 2 decimals, LibreOffice will round the number anyway.

In order to resize the columns select the first 7, and right click on the header choosing "Column Width…". Put in the result you have from the earlier calculation (for me it was 1.35″) and hit OK.

Ideally, after this you should see the dashed line (print are side) after column G. To check how the document looks to the printer, you can preview it by choosing File -> Print Preview from the menu.

Tip: click on the "Margins" item in the toolbar to see the margins of the page.

You might notice there is some text at the top and bottom of the page. The header and footer of the document. To remove them first exit the "Print Preview" window by pressing the Escape key or click on "Close Preview" in the toolbar. Once you are back in the spreadsheet window, go to Format -> Page and then in the "Header" tab uncheck "Header on". Do the same for the Footer.

According to the design of the calendar, the year is placed on the right side, at the top. So in the G1 cell write the year you want, or if you want to have the current year dynamically use this formula instead: =YEAR(TODAY()) . Don't forget to use the same font and size for the year as you did for the month.

We continue by writing the days of the week in the row below the month and year. In my case, the week starts on Sunday. For an example with the week starting on Monday have a look at the A4 calendar template.

Now that we have the top part ready, let's get on with the rest. For that, we need to know what is the first date in our calendar. It's not the 1st of the current month, most of the time, but one of the last days in the previous month. Once we establish that date, we simply add 1(day) to that value until we have six rows. I'll explain why 6 rows, a bit later.

Let's get back to how we're going to get that first date. In the A3 cell we'll start by getting the first day of the month. This is the formula I used =DATE(G1,MATCH(A1,$Settings.A1:A12,0),1) .

The DATE function has 3 parameters: year, month and day. The year we can get from G1. The month has to be a number, but all we have is January, February and so on which LibreOffice does not understand. The trick is to try and find the position of the word "January" in the month list in the "Settings" sheet. Luckily, there's a function for that, MATCH , which needs the value you're looking for(A1 in my case) and the cell range where to look for it(A1:A12 in Settings). The last parameter needs to be "0" in order to only match exact values, using "1" will give you strange results for certain months.

But putting the first day of the month in the first cell is not correct, as most months do not start on a Sunday. But it makes a good starting point.

By knowing the first day of the month we can get the day of the week. In my example it's a Wednesday. The function that returns the day of week from a date is WEEKDAY . The formula is =WEEKDAY(DATE(G1,MATCH(A1,$Settings.A1:A12,0),1)) . You should get a number from 1 to 7. If you don't change the "Number Format" from "Date" to "General".

But why do we need the weekday? The easiest way to explain it is by using a real example. In my case the first day of the month is a Wednesday. The week in my example starts on Sunday, making Wednesday the 4th day of the week. So if January 1st is a Wednesday(the 4th day), Sunday would be 3 days before(the 29th of December). Eventually, we just need to subtract 3 days from the date which is the 1st of the month. This way the formula becomes: =DATE(G1,MATCH(A1,$Settings.A1:A12,0),1) - (WEEKDAY(DATE(G1,MATCH(A1,$Settings.A1:A12,0),1)) - 1) . In practical numbers my example is 01/01 – (4 – 1).

Like mentioned earlier now we only need to add one to the previous row cell. So B3 becomes =A3+1 , C3 becomes =B3+1 and so on until the end of the week.

Next, we skip the 4th row. If you look at the final design, we'll need the rows in between to leave as editable spaces. So the next cell will be A5 and we simply need to add 7 days to A3, resulting in =A3+7 , =B3+7 and so on until the end of the second week. Continuing with that logic, we skip the 6th, 8th, 10th and 12th rows. Also A7 will be =A5+7 , B7 =B5+7 … A13 =A11+7 … you get it.

Tip: if you copy the 5th row and paste it onto the 7th row, LibreOffice will do all the work and you won't have to type as much.

Things are starting to look better, but we don't want the full date for each day. So select all the date cells and right-click on one of them. From the context menu(right-click menu) choose "Format Cells…".

In the "Format Cells" panel, make sure the Date category is selected, it normally should be, automatically. Then in the "Format Code" text field replace the default code(MM/DD/YY) with the letter "D"(it stands for "day"). Click on the green check mark and then on OK.

The next step in styling requires a bit of basic math again. Just the way we had to make sure the 7 columns fit in the print area horizontally, we also need to space the rows to fit in the page vertically.

But before that let's increase the font size of the days. I bumped it up to 12 and made it bold.

Back to the row height issue, let's find out how tall is each row that's not empty. To see the actual height of a row, right-click on the left header where the number is. From the context menu select " Row Height... ".

Get the height values of each row that's not empty and add them up. For me it's 2,28″. The page height is 8.5″. And the top and bottom margins are 0.75″ each, so 1.5″. So 8.5 – 1.5 – 2.28 = 4.72.

Why do we care about this number? If you take a look at the final calendar design, you'll notice that each day number has a space below it where events can be added. So we need to calculate how tall those cells can be. We have 6 rows for the days which means we'll have 6 rows for the events. Dividing 4.72 to 6 gives me 0.786. Given that LibreOffice only uses the first two decimals, I will settle for 0.78″ for the height of the event rows.

Things are looking much better, but we still need some borders. Select the cells like you see in the image below:

Then for applying a border to these cells, right-click the selection and choose "Format Cells…" from the context menu. A panel should open where the "Borders" tab is active. Pick the settings in the following screenshot:

The "Print Preview" result looks like this:

You can leave it like this if it's good for you, but by setting the bottom border to "none" for the days and the same for the top border for events you get a more accurate result.

Editing a border involves clicking in the small preview the border you want to change and then in the Style drop-down selecting the "None" option.

And here is the end result once more:

In case you're wondering how I got the days in the previous and following month to be less visible, I suggest you download the calendar template. If this tutorial will draw some interest, I will explain that part as well.

stephanprookint77.blogspot.com

Source: https://calendartricks.com/how-to-make-a-calendar-in-libreoffice/

Share this post