Using an org-table formula to sum up weekly time cards

Question

I'm filling my time cards in an org-table like below.

Is there a way to fill the column Totals automatically with a formula?

• For the individual lines in a project task, I'd like to sum the hours from Monday to Sunday. I would prefer to keep using a dash to show that I have not spent any hours in a particular day, but that is not top priority.

• For the last line in each week, show the sum of all the hours spend in that week. In the example below, only the weekly sum is shown. But I don't mind if the sum is given also per day.

The table looks like this:

|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| /              |         |         | <      |        |        |        |        |        |        |          |
| 2 0 1 9        | 2 0 1 9 | 2 0 1 9 | 2019   | 2019   | 2019   | 2019   | 2019   | 2019   | 2019   |     2019 |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Description*  |   *CTR* |  *Task* | *Mon*  | *Tue*  | *Wed*  | *Thu*  | *Fri*  | *Sat*  | *Sun*  | *Totals* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 1*       |         |         | 31.des | 1.jan  | 2.jan  | 3.jan  | 4.jan  | 5.jan  | 6.jan  | *Week 1* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Public Holiday |     123 |   12.34 | 7.5    | 7.5    | -      | -      | -      | -      | -      |       15 |
| Project1       |     456 |   56.78 | -      | -      | -      | 7.5    | -      | -      | -      |      7.5 |
| Project2       |     789 |   90.10 | -      | -      | 7.5    | -      | 7.5    | -      | -      |       15 |
|                |         |         |        |        |        |        |        |        | Week:  |     37.5 |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 2*       |         |         | 7.jan  | 8.jan  | 9.jan  | 10.jan | 11.jan | 12.jan | 13.jan | *Week 2* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Project1       |     456 |   56.78 | 7.5    | -      | -      | 3      | 3      | -      | -      |     13.5 |
| Project2       |     789 |   90.10 | -      | 7.5    | 7.5    | 4.5    | 4.5    | -      | -      |       24 |
|                |         |         |        |        |        |        |        |        | Week:  |     37.5 |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 3*       |         |         | 14.jan | 15.jan | 16.jan | 17.jan | 18.jan | 19.jan | 20.jan | *Week 3* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Project3       |     012 |   12.38 | 7.5    | 7.5    | 8.5    | 8.5    | 8.5    | -      | -      |     40.5 |
|                |         |         |        |        |        |        |        |        | Week:  |     40.5 |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|

So far I have tried a simple formula:

#+TBLFM: $11=vsum($4..$10)

But that doesn't do all the work since it doesn't support the dashes and headers and doesn't give me the weekly sum.

Answer 1

Your modifications to the table are quite complex. Maybe it is better to filter the table through a source block.

In the following I include a source block that enters the project sums and the total sums.

There are three major drawbacks.

1. The table is duplicated
2. The minus signs are replaced by zeros. (The table is already passed to the source block in that form. So one can do little about it.)
3. The row with formatting signs is not preserved.

Now you must decide whether you can live with the drawbacks. The first section with the unfiltered table is not exported because of the :noexport: tag. The second section contains the filtered table with the computed results. That section is exported.

* Table of Working Hours (not exported) :noexport:
#+NAME: working_hours
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| /              |         |         | <      |        |        |        |        |        |        |          |
| 2 0 1 9        | 2 0 1 9 | 2 0 1 9 | 2019   | 2019   | 2019   | 2019   | 2019   | 2019   | 2019   | 2019     |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Description*  |   *CTR* |  *Task* | *Mon*  | *Tue*  | *Wed*  | *Thu*  | *Fri*  | *Sat*  | *Sun*  | *Totals* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 1*       |         |         | 31.des | 1.jan  | 2.jan  | 3.jan  | 4.jan  | 5.jan  | 6.jan  | *Week 1* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Public Holiday |     123 |   12.34 | 7.5    | 7.5    | -      | -      | -      | -      | -      |          |
| Project1       |     456 |   56.78 | -      | -      | -      | 7.5    | -      | -      | -      |          |
| Project2       |     789 |   90.10 | -      | -      | 7.5    | -      | 7.5    | -      | -      |          |
|                |         |         |        |        |        |        |        |        | Week:  |          |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 2*       |         |         | 7.jan  | 8.jan  | 9.jan  | 10.jan | 11.jan | 12.jan | 13.jan | *Week 2* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Project1       |     456 |   56.78 | 7.5    | -      | -      | 3      | 3      | -      | -      |          |
| Project2       |     789 |   90.10 | -      | 7.5    | 7.5    | 4.5    | 4.5    | -      | -      |          |
|                |         |         |        |        |        |        |        |        | Week:  |          |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 3*       |         |         | 14.jan | 15.jan | 16.jan | 17.jan | 18.jan | 19.jan | 20.jan | *Week 3* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Project3       |     012 |   12.38 | 7.5    | 7.5    | 8.5    | 8.5    | 8.5    | -      | -      |          |
|                |         |         |        |        |        |        |        |        | Week:  |          |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|

* Second Section
This section is exported.
#+BEGIN_SRC emacs-lisp :var tbl=working_hours :hlines yes :exports results
(defun stringp-match (regexp string &optional start)
  "Do `string-match' with REGEXP beginning at START if STRING is really a string."
  (and (stringp string)
       (string-match regexp string  start)))

(cl-loop
 with total
 with weekdays
 for row in (nthcdr 4 tbl) ;; skip header up to *Description* row
 do
 (cond
  ((eq row 'hline)) ;; skip hlines
  ((stringp-match "\\*Week [0-9]+\\*" (car row)) ;; start of the week
   (setq total 0))
  ((stringp-match "Week:" (nth 9 row))
   (setf (nth 10 row) total))
  ((cl-every #'numberp (setq weekdays (cl-subseq row 3 10)))
   (cl-incf total (setf (nth 10 row) (apply #'+ weekdays))))))
tbl
#+END_SRC

#+RESULTS:
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| 2 0 1 9        | 2 0 1 9 | 2 0 1 9 |   2019 |   2019 |   2019 |   2019 |   2019 |   2019 |   2019 |     2019 |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Description*  |   *CTR* |  *Task* |  *Mon* |  *Tue* |  *Wed* |  *Thu* |  *Fri* |  *Sat* |  *Sun* | *Totals* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 1*       |         |         | 31.des |  1.jan |  2.jan |  3.jan |  4.jan |  5.jan |  6.jan | *Week 1* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Public Holiday |     123 |   12.34 |    7.5 |    7.5 |      0 |      0 |      0 |      0 |      0 |     15.0 |
| Project1       |     456 |   56.78 |      0 |      0 |      0 |    7.5 |      0 |      0 |      0 |      7.5 |
| Project2       |     789 |    90.1 |      0 |      0 |    7.5 |      0 |    7.5 |      0 |      0 |     15.0 |
|                |         |         |        |        |        |        |        |        |  Week: |     37.5 |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 2*       |         |         |  7.jan |  8.jan |  9.jan | 10.jan | 11.jan | 12.jan | 13.jan | *Week 2* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Project1       |     456 |   56.78 |    7.5 |      0 |      0 |      3 |      3 |      0 |      0 |     13.5 |
| Project2       |     789 |    90.1 |      0 |    7.5 |    7.5 |    4.5 |    4.5 |      0 |      0 |     24.0 |
|                |         |         |        |        |        |        |        |        |  Week: |     37.5 |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| *Week 3*       |         |         | 14.jan | 15.jan | 16.jan | 17.jan | 18.jan | 19.jan | 20.jan | *Week 3* |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|
| Project3       |      12 |   12.38 |    7.5 |    7.5 |    8.5 |    8.5 |    8.5 |      0 |      0 |     40.5 |
|                |         |         |        |        |        |        |        |        |  Week: |     40.5 |
|----------------+---------+---------+--------+--------+--------+--------+--------+--------+--------+----------|