When you begin to create formulas in Calc, you soon realize that the wrong kind of data in a cell referenced by your formula can throw the results off. Calc does its best to interpret the data entered according to the formatting style of the cell. If you format the cell as a date type, Calc does its best to translate the input in that cell as a date. At times it is successful and other times not. Fortunately, Calc comes with validity options built in to help you design your spreadsheet in such a way as to give the user help with the input or even force them to enter the data type needed for the cell. In this article, we will look at the validity options and its capabilities. Sheet Setup Before we get to the validity options of Calc, we need to set up a spreadsheet where we will use the options. The spreadsheet is a time sheet for showing the hours worked during a week. The time sheet works well for our example because on a given day, the next time input must have a greater value than the previous entry.

We will start by creating our title and column headers. In cell A1, enter “Time Sheet”. On row 2, fill in columns A-F with the following titles: DAY, IN, OUT, IN, OUT, TOTALS. Now for the row headers. In cell A3, type in “Sunday”. Making sure that cell A3 is highlighted, click on the small square in the lower right corner of the highlighted cell. The cursor will become a crosshair. Drag down to A9. When you release mouse button, the rest of the weekday names are filled in. This happens because the days of the week are one of the default sort lists. You can see all the lists and add your own at Tools > Options > LibreOffice Calc > Sort Lists. Now let's format the cells. Select B3:E9, right-click, and select Format Cells. Click the Numbers tab. From the Category list box, select Time. If you normally use a 12-hour clock, select the “01:37 PM” option under format. However, for a 24-hour clock, select “13:37.” Click OK to save the options. Column F is the totals. We will need to select a time format, at least for the last one, that can go beyond 24 hours. Select cells F3:F10, right-click, select Format Cells. Again, on the Numbers tab, select the Time category, but for the format, select “876613:37.” This will allow for totals that are greater than 24 hours. Click OK to save the settings. If you haven't yet, you might want to save your sheet.

Setup Validation We will need to check the validity of the time inputs. We will want them in a Calc-acceptable time format. This is the only requirement for valid data in the B column. For the other columns (C-E), we will need to also make sure the input is greater than the input before it. Select cell B3, then Data > Validity. The Validity dialog will appear. The Criteria tab (above) is where we control what constitutes valid data. The Allow drop-down list defines the type of data that is acceptable. The default is All Values. The other types are Whole Numbers, Decimal, Date, Time, Cell Range, List, and Text Length. The choices for the different types are basically the same except for Cell Range and List. With the cell Range, you define a cell range and the cell must contain a value that is in one of those cells. The defined range must consist of only one column or one row. With the List option, you define a list of acceptable values. With both the List and Cell Range options, you get a drop-down list box with the acceptable values. For our purposes, we need Allow set to Time.

Check the box for “Allow empty cells” to allow cells to remain empty. This will allow us to start with a blank time sheet. The Data drop-down list allows you to select the comparative operator to use for this validation. The choices are equal, less than, greater than, less than or equal to, greater than or equal to, not equal, valid range, and invalid range. Each one asks for a minimum, maximum, or value to compare, except for the two range choices. The range choices require a minimum and maximum. For our purpose, we need the greater than operator. In the minimum text box, enter 12:00 AM or 00:00. Click on the Input Help tab (below). The settings on this tab are optional. The idea is to provide the user with information about what to enter into the cell as a tip box that pops up when the cell is selected. To activate, click the checkbox for “Show input help when cell is selected.” Create a title for the tip box by typing it in the Title text box. For us, we could use the title “Start Time”. The Input Help text box is where you put the actual help tip like “Enter the start time: ex. 1:00 PM or 13:00”.

Switch to the Error Alert tab (above). Here we can set the validation to display a message when invalid data is entered. To activate the setting, check the checkbox for “Show error message when invalid values are entered”. There are four choices for the Action drop-down list, Stop, Information, Warning, and Macro. Stop, the one we will use, prompts with a dialog and rejects the input when invalid data is entered. For Information and Warning, they prompt with a dialog containing OK and Cancel buttons when invalid data is entered. If the user clicks OK, the input is left as it is. If the user clicks Cancel, the input is rejected. Macro will allow you to select a macro to run when invalid data is entered. The Title and Error Message is the title and message for the dialog box. We could set them to “Invalid Time” and “Enter in time format: 13:00 or 1:00 PM”. Once you have everything set, click OK to save the Validity dialog settings.

We will copy cell B3 to cells B4:B9. Right-click cell B3 and select Copy. Select cells B4:B9, right-click, and select Paste. This will copy the validity settings to the cells. The whole column will have the same requirements for input. For columns C, D, and E, we will do almost the same thing. We will go through the steps on C, and you can repeat for D and E. Select cell C3, Data > Validity. On the Criteria tab of the Validity dialog, select Time from the Allow drop-down list. Check to allow empty cells. Set Data to greater than, and set the minimum value to B3. Notice that we use a relative cell reference here. This will allow us to copy it to the other cells and have the reference translate. These criteria tell Calc that the cell requires a value greater than the value in cell B3. Do what you want with the help and error tabs. I do recommend using at least Information or Warning messages for invalid data. The messages could mention needing a greater value (or a later time). Right-click cell C3 and copy the cell, then paste it into cells C4:C9. You can repeat for columns D and E. You can also just copy C3 to D3:D9 and E3:E9, but you will need to adjust the text in the dialogs for help and error.

Total Formulas Now that we have our validation set up, we need to create the formulas to give us our totals. We will start with the daily totals. Select cell F3. Enter the formula =(C3-B3)+(E3-D3) which will give us the total for Sunday's times. Right-click F3 and Copy. Select cells F4:F9, right-click, and Paste. For the weekly total in F10, we will just sum the daily totals. Here is the formula: =SUM(F3:F9)

Protect Cells Once you have a sheet set up the way you like, you may want to protect the parts of the sheet you don't want changed like the labels and formulas. First, you need to tell Calc whether the cell is protected or not. Then you add protection to the sheet or document once everything is finished. In the case of our time sheet, we need to protect everything except for the cells where the user enters their times, B3:E9. By default, the Protect option is turned on for all cells. To change the protection setting for the input cells, select B3:B9, Format > Cells, and the Format Cells dialog appears. Click on the Cell Protection tab and uncheck Protected.

At this point, you can still edit any cell because the sheet or document is not protected. To turn on protection for the sheet or document, Tools > Protect Document > Sheet (or Document). The Protect Sheet or Protect Document dialog will appear depending on which option you selected. Enter and confirm a password in the dialog. The options will allow you to check whether the user can select protected or unprotected cells. If you do not enter a password, then no password is required to remove protection from the sheet or document. To remove protection from a sheet or document, Tools > Protect Document > Sheet (or Document). If a password was used when protection was set, enter the password used to protect the sheet or document.

Mark Invalid Data with Detective If you selected Warning or Information from the Action drop-down list in the Validity dialog, a user could enter invalid data and click OK to keep the invalid input. This may generate strange results in your totals or an error. Tools > Detective > Mark Invalid Data will mark the invalid data. Once the user corrects the data, Tools > Detective > Mark Invalid Data should clear the marks, unless the data is still invalid. Notice from my sample that invalid data can cause other cells data to show as invalid. The Calc Validity options are a great way to set up sheets for use by other people, or to keep you from entering invalid data on a complicated sheet. You can set it up to reject the invalid data outright, or for the user to make a choice about keeping the input. Once you have the sheet set the way you want it, you can protect the sheet from unwanted changes. This is a good way to protect formulas and labels. Finally, the Detective tool allows the user to mark invalid data they entered.