LibreOffice Tutorial: Make a Time Schedule – Part 2 – the ‘IF’ formula

I moved my website to another web-host and during the process I did not realised that I transferred my word-press multi-site content to a word-press single site content. Along with a long summer looking after the children and back to school rush, I am only able now to write the next part of this tutorial… So sorry if you were waiting for it.

NOTE: I have upgraded to LibreOffice 6.0, however it should works exactly the same with LO 5.

Before I went to bed last time we were building the logic to let LibreOffice know what Activity should be displayed now.

To be able to build this logic we need:

1. 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.
   (Right click on Column A header and select “Insert Columns Left”)
2. 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.
3. Formula to get the current Date & Time.
   To actually get the current date and time, that is what we are going to put at the top of column A.
   The function to get the current date is  ‘NOW()’, so in Cell A1 write:
   =NOW()
   We will change that formula later when we want to only display the current time.
   Once you  have entered this the cell displays date and time.
4. Formula to compare time.
   To write the formula in A2 that checks the current time and date holds by cell A1 is between Start date in B2 and End date in C2.
   To understand how it works I am going to do that part step by step:
   1. The IF formula and its parameters.
      In cell A2, we compare if current time & date in A1 is greater than start date in B2:
      =IF(A1>B2,1,0)
      This is a IF formula. It allows us to test if a condition is true or false. The syntax is simple:
      IF(condition,  result if the condition is true, result if the condition is false). So if A1>B2 it returns 1, else it returns 0.
      Hold on a minute… This formula will displays a 1 whatever the time is.
      Indeed if I type in B2 a bigger time than the time in A1 I still get a ‘1’. (For example if current time is 15:10:42 and if I put 16:00:00 in B2, I still get the formula telling me that the Value in A1 is bigger than B2.
      This is because we are comparing a ‘date and time’ value in A1 with a ‘time’ value in B2. So we actually need to extract only the time from the date and time in A1.
   2. Formula to get only the current time and not the current date.
      To extract the time only from A1, the easiest way is to do it straight away in A1 by using 3 functions combined with NOW():
      TIME(), HOUR() and MINUTE(). I will add a 4th function later that will display time to the nearer 5 minutes (e.g. 11:00, 11:05, 11:10…).
      So in A1, instead of ‘=NOW()’ you should write:
      =TIME(HOUR(NOW()),MINUTE(NOW),0)
      How this works:  TIME reconstruct a time value HH:MM:SS from 3 integers parameters (hour, minute, second)
      HOUR(NOW): extract only the hours from the Date and Time given by ‘NOW()’.
      MINUTE(NOW): similarly extract only the minutes from ‘NOW()’.
      0: I decided to put zero for the seconds as I don’t need to have an activity plan so strict that at if I am one second late I feel bad!
   3. Cell Format.
      Another thing to change is the display format of cell A1. If you still have the default cell format you will see on your screen in A1 something like:
      

      30/12/99 23:30

With a weird date. This is because LibreOffice is still trying to display a date and time from a time value. So to have only the time displayed you should change the cell  format the same way  we did it for our columns ‘Starts’ and ‘Ends’ ([HH]:MM:SS see in the previous tutorial).
So now you should have something like this:

$ to lock a cell.
Before sliding down the formula in A2, there is one thing that we need to change:
If we do slide down the formula that is in A2:”=IF(A1>B2,1,0)”, we get in Cell A3: “=IF(A2>B3,1,0)”. Which means for each cell in column A we are comparing the value of the cell just above with the cell in column B.
So we need to ‘lock’ A1 when we slide the down the formula.
We can do so by adding a ‘$’ in front of the column letter and another ‘$’ in front of the column number. So in A2, instead of ‘=IF(A1>B2,1,0)’ we should type:
=IF($A$1>B2,1,0)

Writing tutorials takes time! My activity plan once again shows  ‘Zzzzz’, it’s late so let’s stop here. Next part of this tutorial series is about nested ‘IF’ and ‘AND’ function.

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

Thank you!