LibreOffice Tutorial: Make a Time Schedule – Part 1 – Naming sheet, cell format and building logic for a simple agenda

tutolibro.tech > LibreOffice > Calc > LibreOffice Tutorial: Make a Time Schedule – Part 1 – Naming sheet, cell format and building logic for a simple agenda

I am starting here a series of tutorials on LibreOffice.

Other parts:
In Part 1 we look at renaming a sheet, entering some random data, formatting cells and starting to build the logic of the agenda.

In Part 2 of this tutorial we write some time functions and IF formulas.

In Part 3 of this tutorial we learn about nested IF and AND function.

In Part 4 of this tutorial we learn about VLOOKUP.

In Part 5 we look at how to create and use a ‘named range’.

In Part 6, we learn the INDEX function.

In Part 7, we learn how to code a (very) simple LibreOffice Macro.

In Part 8, we use the MROUND function to round the time.

In Part 9 part we do some debugging and learned about the ISERROR and NOT functions.

In Part 10 part we add some color and formatting to wrap-up this series.

LibreOffice is my favourite office software! And also because it is not always easy to find free complete tutorials on LibreOffice.

If you don’t know already LibreOffice is the equivalent of Microsoft Excel/Word/Access/Power-Point office suite. With LibreOffice you can almost do all the things you can do with the paid-for Microsoft Office suite. LibreOffice is what is called a ‘fork’ derived from OpenOffice developed by Apache which actually originated from StarOffice developed by Sun Microsystem as an alternative to the Microsoft successful Office Suite. I actually used to help StarOffice Customers a while ago.

LibreOffice is completely free and open source, and has a very big community of users and developers, you can indeed find an answer to pretty much any problems and question related to the free software suite.

So I am going to explain a few formulas and a simple macro which together will make a not very sophisticated but useful time schedule. I am first going to explain step by step how to make your own daily time schedule then I will share the file.

Note: All formulas and the macro should work with OpenOffice as well. The formulas should also be able to work in Excel but probably not the Macros. However it is not very hard to write an equivalent macro for Excel.

Get LibreOffice:

If you don’t have LibreOffice (or OpenOffice) already and you want to give it a go, download from libreoffice website. This tutorial was written with Version 5 but it should work with newer version too. If you have any problem with it working leave me a comment and I will help you to sort it out.

How it works?

Basically the spreadsheet has two main sheets for each schedule, for example 2 for my schedule and 2 for my children schedule. One Sheet is the actual daily schedule displayed, the front-end, and the other sheet is the back-end with all the possibles entries to be displayed:

Rename Sheet1 to ‘Now!’, Sheet2 to ‘planner’.

Rename new sheet to ‘Now!’

You will be able to copy these two sheets to create new Schedule, once we have this base working.
The spreadsheet named ‘Now!’ is the displayed schedule for the day.
The spreadsheet named ‘planner’ is where the data for the time schedule is stored.

Enter dummy data:

The planner sheet needs to have the list of all the daily activities with their starting and ending time, we’ll start with dummy data, as we need to focus on the mechanism of the schedule, not its content yet, to first make sure that everything works, here is an example:

Start	Ends	Activity
00:00:00	06:30:00	Zzzzzzz
06:30:00	07:30:00	Wake up
07:30:00	09:00:00	Breakfast and Children to School
09:00:00	24:00:00	Make a time schedule using LibreOffice!

And in the spreadsheet it’d look like that:

Problem?

You get the 24:00:00 displayed as 00:00:00 even if it says 24:00:00 in the formula bar?
Such display issues are always due to the cell formatting. In this case we need a slightly different Time format. LibreOffice detected a Time format when you entered 24:00:00, because of the ‘:’ .
But the default Time Formatting makes 24:00:00 as 00:00:00 because it is displaying as a Clock Time like on your digital alarm clock.
And I have never seen 24:00:00 on my alarm clock, not because I am always already asleep by then, but because after 23:59:59 it goes to 00:00:00 and the date changes… I don’t now how to stop this to happen on my alarm clock but in a LibreOffice cell you simply need to set a custom Time format code like that:
[HH]:MM:SS instead of HH:MM:SS

So right click on the cell, select Format Cell.
Then in the Number Tab, select Time, and in the ‘Format Code’ Text box simply put these squared brackets around HH. And actually do it for the entire columns A and B, by selecting both columns A and B (click on A then hold shift and click on B).

Working the Logic, building the formulas:

To be able to display automatically the activity that is matching the current time we are going to need to compare the current time with the starting time and with the ending time of each activity using the following logic:

Is This Activity the Current Activity?:
IF Current time is equal or greater than Start Time of This Activity
AND
IF Current time is smaller than End Time of This Activity
THEN:
YES: This Activity is the Current Activity, 
OTHERWISE:
NO: This Activity is not the Current Activity.

To be able to build this logic we need to put somewhere the answer to the question: Is This Activity the Current Activity?
So insert a column in front of Column A and don’t put anything at the top yet.

This Activity: it is the activity the formula is going to look at, so basically it is the content of any cell in Column D, that was actually Column C before.

My Schedule says “Zzzzzz” I just seen the 24:00:00 so I will need to finish this tutorial later as I am falling asleep now!

If you like this tutorial you could buy me a coffee to help me continue writing add-free tutorials.

Thank you!