This is the 3rd part of my LibreOffice Tutorial to learn about simple useful formula 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.

So we have in **A2** the following formula that we can drag down to match with the number of cells in column **B**:

**=IF($A$1>B2,1,0)**

In Column **A**, your formulas should look like these:

Row \ Colum | A |
B | C | D |

1 | =TIME(HOUR(NOW()),MINUTE(NOW()),0) |
Start | Ends | Activity |

2 | =IF($A$1>B2,1,0) |
00:00:00 | 06:30:00 | Zzzzzzz |

3 | =IF($A$1>B3,1,0) |
06:30:00 | 07:30:00 | Wake up |

4 | =IF($A$1>B4,1,0) |
07:30:00 | 09:00:00 | Breakfast and Children to School |

5 | =IF($A$1>B5,1,0) |
09:00:00 | 15:00:00 | Make a time schedule using LibreOffice! |

6 | =IF($A$1>B6,1,0) |
15:00:00 | 21:00:00 | Kids |

7 | =IF($A$1>B7,1,0) |
21:00:00 | 22:00:00 | Finally free! |

8 | =IF($A$1>B8,1,0) |
22:00:00 | 24:00:00 | Zzzzzzz |

The results from the formula at this time of the day (21:58:00) for me are as follow:

Row / Column | A | B | C | D |

1 | 21:58:00 | Start | Ends | Activity |

2 | 1 | 00:00:00 | 06:30:00 | Zzzzzzz |

3 | 1 | 06:30:00 | 07:30:00 | Wake up |

4 | 1 | 07:30:00 | 09:00:00 | Breakfast and Children to School |

5 | 1 | 09:00:00 | 15:00:00 | Washing-up, laundry, sport, lunch, meditation. |

6 | 1 | 15:00:00 | 21:00:00 | Kids |

7 | 1 | 21:00:00 | 22:00:00 | Finally free! |

8 | 0 | 22:00:00 | 24:00:00 | Zzzzzzz |

Only Cell **B2**(22:00:00) is greater than cell **A1**(21:58:00) but not for long…

So our formula (**=IF($A$1>B2,1,0)**) is only comparing if the current time stored in cell **A1** is greater than the ‘Start’ time in **B2**.

It should also check that **A1** is smaller than the ‘Ends’ cell **C2** that stores the time when the activity in cell **D2** ends.

The formula to check if **A1** is smaller than **C2** is very similar to the one we already have, we just need to use the ‘smaller’ sign instead of greater sign and **C2** instead of **B2**:

**=IF($A$1<C2,1,0)**

And in order to test both greater and smaller we are going to do what is called a **nested IF**. It is simply an **IF** within another **IF**.

We can reuse our first **IF** formula to test if **A1** is greater than **B2** (**=IF($A$1>B2,1,0)**) but instead of the 1, which is what to display when the condition is true, we put the other **IF** that test if **A1** is greater than **C2**!

This is what it look like:

**=IF($A$1>B2,IF($A$1<C2,1,0),0)**

The formula is doing the following:

If **$A$1** is greater than **B2** then If **$A$1** is smaller than **C2** then display 1 , else display 0.

There is actually another prettier way (I think) to do the same thing which is using a **AND **function in the condition of the if:**AND** function simply do a logical **AND** between 2 conditions (or more) with the following syntax: **AND**(*condition 1* , *condition 2*)

If we apply **AND** to our logic we should use the following formula in **A2**:

**=IF(AND($A$1>B2,$A$1<C2),1,0)**This time the formula goes like that:

If

**$A$1**is greater than

**B2 AND**

**$A$1**is smaller than

**C2**then display 1 else display 0.

I like it because it is shorter and looks more like a real statement.

To make it simpler I also like an even shorter formula to achieve the same result, which does not involve any **IF** statement but use the fact that the AND function does return a result:

**=AND($A$1>B2,$A$1<C2)**This formula using function

**AND**is giving back a ‘TRUE’ if the two conditions are true and a ‘FALSE’ in any other situations. So to get a ‘TRUE’, conditions

**$A$1>B2**

**AND**

**$A$1<C2**have to be true indeed.

This is great because it is shorter to type and faster for LibreOffice to calculate (even if it does not really matter as we are not manipulating millions of rows of data).

Another way to get the same result is just doing the same formula with this time using the **IF** in both conditions within the **AND** without specifying any result to display:

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

So now you can pick up your favourite formula between these 4!

When you write a formula (and the same goes for macro), ask yourself if some other people will have to look and understand your spreadsheets. You may even want yourself to look at your work later in your life and need to understand these formulas. My point is that it is OK to use a very short and effective formula like the simple **AND**() if you remember what it does, but it may be better to use the **IF(AND())** function as it sounds and look a bit more like ‘human’ language and is easier to understand.

In the next part of this tutorial we will be looking at a very useful function called **VLOOKUP **to help us with finding the current activity in order to display.