This is the part number 8 of my LibreOffice Tutorial to learn about simple useful formulas and a tiny bit of macro to make a simple daily agenda.
In the first part we looked at renaming a sheet, entered some random data, formatted cells and started to build the logic of the agenda.
In the second part of this tutorial we wrote some time and IF formulas.
In the third part of this tutorial we learned about nested IF and AND function.
In the fourth part of this tutorial we learned about VLOOKUP.
In the 5th part we looked at how to create and use a ‘named range’.
In the 6th part, we learned the INDEX function.
In the 7th part, we learned how to code a (very) simple LibreOffice Macro.
This is the 8th and final part of the tutorial and we are going to use the MROUND function.
Before starting a useful l thing to do is disabling the event handler that triggers the macro recalculating the time every time we click somewhere. It does make it a bit strange to edit formulas in the cell. So simply right click in the sheet ‘Now!’ select ‘Sheet event’ in the context menu and ‘Remove’ the action associated to ‘Selection Changed’. To put it back refer to part 7 of this tutorial.
The first thing I want to do is to do is to correct the formulas that checks if the current time is included between start time and end time of the current activity.
In part 3 of this tutorial we used the following formula:
You may have noticed that when the clock is on the exact 0 minute of an hour we do get an “N/A#” Activity displayed…
You can reproduce the error by first copying the formula that is in cell A1 in sheet planner, in another empty cell to keep it safe (the formula has no reference to any other cell so it can go anywhere), and then write in A1 23:00:00 instead of the formula. Remember to copy the right formula back in A1 once you have done your testing.
Why is it happening?
In our conditions we are using both the ‘greater than’ (>) and the ‘smaller than’ (<) signs, so the time in $A$1 is 21:00:00 exactly, it’s neither bigger than B2 nor smaller than C2, so our formula returns logically a N/A# which means “Not Available”.
To correct that we simply need in the condition to include the exact hour with either a ‘greater or equal to’ condition (‘>=’) or a ‘smaller or equal to’ (‘<=’) but not both at the same. My preferred solution is then:
But you could also do:
And of course remember to drag down the formula to have it applied to the other cells below.
The second change I want to make is to amend the formula in $A$1 of the planner sheet to round up time to the nearest 5 minute:
To do that, in $A$1 instead of:
I use in place of the minutes the function MROUND().
MROUND() returns a number rounded to the nearest multiple of another number.
This function takes in first argument the number to round, so here the minute of NOW() and in second argument the number that the result needs to be the closest to, here we have chosen 5.
So the cut-off time between two activities in the display is around 2 minutes and 30 seconds before and after the actual time an activity starts in the planner_daddy list.
This is what my planner sheet looks like ( with the ‘Show formula’ view on ):
|Row \ Colum||A||B||C||D|
|4||=AND($A$1>=B4,$A$1<C4)||07:30:00||09:00:00||Breakfast and Children to School|
|5||=AND($A$1>=B5,$A$1<C5)||09:00:00||15:00:00||Make a time schedule using LibreOffice!|
In the next chapter we will be looking at reproducing and correcting a little bug in the logic.