Author: Dmitri Popov
If you want to keep tabs on your deadlines, you don’t need a fancy project management application — often, a simple spreadsheet can do the job. To see how, let’s create a spreadsheet that tracks task deadlines, shows the current status of each task, and highlights scheduling conflicts. In the process we’ll learn a few useful Calc techniques.
To keep things simple, we’ll create a separate sheet for each month, with three columns: Task, Deadline, Days left, Status, and Conflict. The Status column might hold values such as “In Progress” or “Completed.” Depending on the current status, the cells in the Days left column will display either the number of days to the deadline or “OK.” If the deadline for the task has passed but the article’s status is not “Completed,” the Days left column will display “OVERDUE,” making it easier to quickly locate unfinished and overdue tasks. Finally, we’ll use the Conflict column to identify scheduling conflicts: if two tasks have the same deadline date, the Conflict cell of the second task will display a “CONFLICT” warning (ideally, the spreadsheet should mark both conflicting tasks, but I’m still working on how this can be done).
The key elements of what we’ve described are two formulas in the cells of the Days left and Status columns. Let’s take a look at the Days left formula first:
To better understand how that works, let’s break it into several logical parts. The formula itself is based on the IF function, which uses the following format:
IF(Test; The_value; Otherwise_value)
In our case, the test part checks whether the number of days is less than 0 (i.e., whether the deadline has passed) and the status is not “Completed.” If both conditions are met, then the value of the D2 cell (the Days left column) is set to “OVERDUE.” Otherwise, the formula runs another IF function that sets the value of cell D2 to “OK” if the article’s status is “Completed”; otherwise it sets the value to the number of days left to the deadline.
Now on to the formula used in the Conflicts column:
This formula uses three functions: IF, ISNA, and MATCH. The latter allows us to compare values in an array of cells. The ISNA function then checks the result returned by MATCH, and if it contains no matches, the formula sets the value of the cell in the Conflicts column to “OK”; otherwise it sets the value of the cell to “CONFLICT.”
Once you’ve specified both formulas for a single row, you can apply them to other cells in the Days left and Conflicts columns by selecting the cell with the formula and dragging the selection handle over other cells in the row.
Although the spreadsheet is ready to go, there are a couple of things you can do to make it more efficient and easy to use. For starters, you can turn the cells in the Status column into a drop-down list containing predefined values. To do this, select the Status column and choose Data -> Validity, and select List from the Allow drop-down list. Specify status items in the Entries field and press OK.
You might also want to spice up the spreadsheet by applying conditional formatting to the cells in the Days left and Status columns. For example, you can specify conditional formatting that displays the “OVERDUE” warning in red bold font on a yellow background. To do this, use the Stylist (press F11 to evoke it) to create a new style using with the described formatting and save it as “Overdue.” Select the first cell in the Days left column and choose Format -> Conditional Formatting, and specify the following condition:
Cell value - is equal to - "OVERDUE" Cell Style - Overdue
In a similar manner, you can specify conditional formatting for the cells in the Conflicts column.
To make data entry easier, you can use two extensions: DataForm and Date Browser. The former adds a data entry form that makes it easier to enter data in cells, while you can use the latter to quickly enter a date in a cell in the Deadline column using the Date picker pop-up window.
That’s all there is to it. Obviously, this solution doesn’t rival a dedicated project management application, but it can help you to keep track of your tasks and deadlines with minimum fuss.
- Office Software