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?

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.

| improve this answer | |
  • 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 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 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 at 3:42
  • @NickD thanks, fixed. – xuchunyang Oct 24 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.

| improve this answer | |
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

| improve this answer | |

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.