This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature: Office Software

Using Calc to manage schedules

By Dmitri Popov on October 31, 2008 (6:00:00 PM)

Share    Print    Comments   

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:

IF(DAY(B2)-DAY(TODAY())<0 AND (D2<>"Completed");"OVERDUE";IF(D2="Completed";"OK";DAY(B2)-DAY(TODAY())))

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:

IF(ISNA(MATCH(B2;B3:B15;0));"OK";"CONFLICT")

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.

Dmitri Popov is a freelance writer whose articles have appeared in Russian, British, US, German, and Danish computer magazines.

Share    Print    Comments   

Comments

on Using Calc to manage schedules

Note: Comments are owned by the poster. We are not responsible for their content.

Conflicts

Posted by: Anonymous [ip: 75.121.38.205] on November 01, 2008 12:22 PM
One way to get a CONFLICT flag on every conflict is to change to the COUNTIF function, i.e.
IF(COUNTIF(B$2:B$15;B2)=1;"OK";"CONFLICT")
I include the $ in my formulas to make copy and paste easier.

#

fancy project management applications?

Posted by: Anonymous [ip: 172.18.91.232] on November 07, 2008 10:24 AM
Could anyone please suggest GTK+ or QT applications that roughly allow one to perform the tasks described in this article?

#

Re: fancy project management applications?

Posted by: Anonymous [ip: 172.18.95.211] on November 08, 2008 09:45 AM
I found myself two leads: ANPSEDIC [1] and intern-tracker [2]. The first, download from a site other than the official (some problems with the French administration concerning programme name) and use through Wine, and second is web based.

Perhaps someone knows similar applications, using usual GTK+ or QT toolkits? As to OO Calc, I would rather avoid using spreadsheets for just about anything from `=2+2' to gaming.


[1] http://www.anpsedic.org/
[2] http://sourceforge.net/projects/intern-tracker/

#

Re(1): fancy project management applications?

Posted by: Anonymous [ip: 172.18.95.211] on November 08, 2008 09:55 AM
I managed to download anpsedic from here [1]. The programme is Freeware; to the best of my knowledge, the author was threatened against diffusing the programme (name clash), but downloading it --- if you found it --- should stay legal. To use it, you will have to learn French, though.

[1] http://static.commentcamarche.net/www.commentcamarche.net/download/fichiers/Anpsedic_2.1.3_3320.exe

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya