LibreOffice Tutorial – Part 8 – MROUND

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 this part 8 of the tutorial 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:

=AND($A$1>B2,$A$1<C2)

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:

=AND($A$1>=B2,$A$1<C2)
But you could also do:

=AND($A$1>B2,$A$1<=C2)

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:

=TIME(HOUR(NOW()),MINUTE(NOW()),0)

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.

=TIME(HOUR(NOW()),MROUND(MINUTE(NOW()),5),0)

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
1	=TIME(HOUR(NOW()),MROUND(MINUTE(NOW()),5),0)	Start	Ends	Activity
2	=AND($A$1>=B3,$A$1<C2)	00:00:00	06:30:00	Zzzzzzz
3	=AND($A$1>=B3,$A$1<C3)	06:30:00	07:30:00	Wake up
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!
6	=AND($A$1>=B6,$A$1<C6)	15:00:00	21:00:00	Kids
7	=AND($A$1>=B7,$A$1<C7)	21:00:00	22:00:00	House Chores
8	=AND($A$1>=B8,$A$1<C8)	22:00:00	23:00:00	Finally free!
9	=AND($A$1>=B9,$A$1<C9)	23:00:00	00:00:00	Zzzzzzz

In the next chapter we will be looking at reproducing and correcting a little bug in the logic.

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

Thank you!