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

Following my short series of article about time management (on my website superbusydaddy.com) , I wanted to share with you what tool I am using to make my life a bit easier: LibreOffice Free open source software.

So I am starting here series of articles on LibreOffice because it’s my favourite office software.

If you don’t know already LibreOffice is the equivalent of Microsoft Excel/Word/Access/Power-Point office suite. With LibreOffice you can do pretty much the same. LibreOffice is what is called a fork derived from OpenOffice from 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 Customer 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 formula should be able to work in Excel but probably not the Macro. However it should be very easy to have the same macro in Excel too and I may  put the equivalent in Excel in a footnote later.

Get LibreOffice:

If you don’t have LibreOffice (or OpenOffice) already and you want to give it a go, download from libreoffice website. You will then get LibreOffice Version 6 (which by the way looks awesome and I am going to upgrade to 6 soon). I have Version 5 but it will work the same.

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’.

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

Enter dummy data:

The planner sheet needs to have the list of all the daily activities and their starting and ending time, we’ll start with dummy data to not focus on the 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 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!