0

I have two tables

#+NAME: tasks
| name   | spent  |
|--------+--------|
| task 1 |        |
| task 2 |        |
| task 3 |        |
| task 4 |        |
| task 5 |        |
#+NAME: works
| date             | task   |     time |
|------------------+--------+----------|
| <2020-10-17 Sat> | task 1 |     0:05 |
| <2020-10-18 Sun> | task 1 |     0:10 |
| <2020-10-19 Mon> | task 2 |     1:00 |
| <2020-10-20 Tue> | task 3 |     1:25 |
| <2020-10-21 Wed> | task 4 |     0:10 |
| <2020-10-22 Thu> | task 4 |     2:30 |

How to calculate total time spent to task in table tasks column spent using org-lookup-all?

0
1

The code works for me:

(org-table-time-seconds-to-string
 (apply '+
        (mapcar 'string-to-number
                (mapcar 'org-table-time-string-to-seconds
                        (org-lookup-all
                         $1
                         '(remote(works,@2$2..@>$2))
                         '(remote(works,@2$3..@>$3))))))
 'hh:mm)

org-lookup-all returns something like ("0:05" "0:10").


#+NAME: works
| date             | task   |     time |
|------------------+--------+----------|
| <2020-10-17 Sat> | task 1 |     0:05 |
| <2020-10-18 Sun> | task 1 |     0:10 |
| <2020-10-19 Mon> | task 2 |     1:00 |
| <2020-10-20 Tue> | task 3 |     1:25 |
| <2020-10-21 Wed> | task 4 |     0:10 |
| <2020-10-22 Thu> | task 4 |     2:30 |

#+NAME: tasks
| name   | spent |
|--------+-------|
| task 1 | 00:15 |
| task 2 | 01:00 |
| task 3 | 01:25 |
| task 4 | 02:40 |
| task 5 | 00:00 |
#+TBLFM: $2='(org-table-time-seconds-to-string (apply '+ (mapcar 'string-to-number (mapcar 'org-table-time-string-to-seconds (org-lookup-all $1 '(remote(works,@2$2..@>$2)) '(remote(works,@2$3..@>$3)))))) 'hh:mm)

I learnt how to use the function via https://orgmode.org/worg/org-tutorials/org-lookups.html, the webpage contains some examples.

4
  • Thank you some much. It works as expected. But how did you find out about functions org-table-time-seconds-to-string and org-table-time-string-to-seconds? I have tried to find something like these but nothing have found
    – osadasami
    Oct 23 '20 at 11:32
  • 1
    @osadasami 1) Most Spreadsheet apps support the time&date datatype and provides functions to work with them, 2) then I search "time" in (info "(org) Top"), and notice it does support duration (info "(org) Durations and time values"), so org mode must provides utility functions for time/duration, and the info page mentions the variable org-table-duration-hour-zero-padding 3) C-h v on the variable then jump to the source file org-table.el, then search functions about time via M-x helm-imenu time, this is how I found these two functions.
    – xuchunyang
    Oct 23 '20 at 11:50
  • A small nit: can you fix the final sentence of your answer? It's missing a verb but I don't know which one :-) Otherwise, thanks for the answer (and that link).
    – NickD
    Oct 24 '20 at 3:42
  • @NickD thanks, fixed.
    – xuchunyang
    Oct 24 '20 at 10:26
0

In Calc (the Emacs embedded calculator) there is a format for hours-minutes-seconds that you may leverage. For instance, 1 hour, 25 minutes, 17 seconds is written 1@ 25' 17". Here is a variation of xuchunyang's answer with this format:

The input table:

#+NAME: works
| date             | task   | time      |
|------------------+--------+-----------|
| <2020-10-17 Sat> | task 1 | 0@ 05' 0" |
| <2020-10-18 Sun> | task 1 | 0@ 10' 0" |
| <2020-10-19 Mon> | task 2 | 1@ 00' 0" |
| <2020-10-20 Tue> | task 3 | 1@ 25' 0" |
| <2020-10-21 Wed> | task 4 | 0@ 10' 0" |
| <2020-10-22 Thu> | task 4 | 2@ 30' 0" |

The sums:

#+NAME: tasks
| name   | spent     |
|--------+-----------|
| task 1 | 0@ 15' 0" |
| task 2 | 1@ 0' 0"  |
| task 3 | 1@ 25' 0" |
| task 4 | 2@ 40' 0" |
| task 5 | 0         |
#+TBLFM: $2='(math-format-value (calcFunc-vsum (cons 'vec (mapcar #'math-read-expr (org-lookup-all $1 '(remote(works,@2$2..@>$2)) '(remote(works,@2$3..@>$3)) )))))

Note that Org Mode tables understand this @ ' " format. Therefore you may use it in other ways.

0

Alternately, you may want to look at the OrgAggregate package available on Melpa. It does all kinds of aggregations. In your example with the @ ' " format, it will use the task column as a key, and sum up the time column values. You don't have to create the receiving table, OrgAggregate takes care of it.

#+BEGIN: aggregate :table "works" :cols "task vsum(time)"
| task   | vsum(time) |
|--------+------------|
| task 1 | 0@ 15' 0"  |
| task 2 | 1@  0' 0"  |
| task 3 | 1@ 25' 0"  |
| task 4 | 2@ 40' 0"  |
#+END:

Documentation here: https://github.com/tbanel/orgaggregate

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.