0

How to calculate time durations in tables?

Subtraction of numerical vectors works directly:

| day              | t1       | t2       | differences |
|------------------+----------+----------+-------------|
| [2021-04-08 Thu] | [10, 13] | [16, 18] | [6, 5]      |
#+TBLFM: $4=$3-$2

Using this with time vectors to compute durations doesn't work. It computes only the first item:

| day              | time 1         | time 2         | durations |
|------------------+----------------+----------------+-----------|
| [2021-04-08 Thu] | [10:45, 14:00] | [12:00, 15:00] |     01:15 |
#+TBLFM: $4=$3-$2;U

I would expect this:

| day              | time 1         | time 2         | durations     |
|------------------+----------------+----------------+---------------|
| [2021-04-08 Thu] | [10:45, 14:00] | [12:00, 15:00] | [01:15, 1:00] |

I hope there is an (easy) solution (?)


EDIT: The n-th item of 'time 2' would be always larger than the n-th item of 'time1'. I don't know if this makes it simpler.


EDIT2: I've tried it with elisp. My idea was to convert the time into minutes, so 10:45 should be converted to 10x60+45=645. I've found an org function for it, the evaluation of the following line is "645.0".

(org-duration-to-minutes "10:45") 

Then I can make the "vector subtraction" with the numbers, then I just have to reconvert the results. There is the function org-duration-from-minutes for this purpose.

That was the idea but I wasn't able to let it work in the table. I'm not familiar at all with it. Even this simple formula doesn't work -- "ERROR":

| day              | time 1         | time 2         | time2 in minutes | durations |
|------------------+----------------+----------------+------------------+-----------|
| [2021-04-08 Thu] | [10:45, 14:00] | [12:00, 14:00] | #ERROR           |           |
#+TBLFM: $4='(mapcar 'org-duration-to-minutes $3)

I would expect "[720.0, 840.0]". (This would be an intermediate step.) I also tried to change the format into "10:45 14:00" or "(10:45 14:00)" or "(10:45, 14:00)".. It didn't work.


EDIT 3: I've found a solution. See below.

2
  • I'm pretty sure that the evaluation code for table formulas is too simple to be able to do this: in particular, the application of the U format assumes that the element it is applied to is a scalar. I'd love to be proven wrong though.
    – NickD
    Apr 9 at 15:12
  • @NickD You mean it must be some "complex" elisp formula (or calc formula)? I've tried it. (EDIT 2) But obviously I need more knowledge about elisp or the syntax of tables.
    – rl1
    Apr 9 at 15:56
2

I've found a solution. It's acceptable for me. I don't know if it's useful for you. Anyway I'm sharing it.

I changed the format a bit (one column for the times, not two), I think it is more practical.

Example: Workflow for a day:

| work | workflow                           | single durations |  sum |
|------+------------------------------------+------------------+------|
| w1   | 9:15-10:16 13:00-13:05 16:08-17:19 | (1:01 0:05 1:11) | 2:17 |
| w2   | 11:13-12:00                        | (0:47)           | 0:47 |
| w3   | 14:05-15:03 17:34-19:03            | (0:58 1:29)      | 2:27 |
#+TBLFM: $3='(format "%s" (durations-HHMM $2))::$4='(sum-durations $2)

I use these small functions:

  (defun single-duration-minutes (s)
    (let ( (l (split-string s "-")) )
      (- (org-duration-to-minutes (nth 1 l)) (org-duration-to-minutes (nth 0 l)))))
  (defun durations-HHMM (s)
    (mapcar 'org-duration-from-minutes (mapcar 'single-duration-minutes (split-string s))))
  (defun sum-durations (l)
    (org-duration-from-minutes (apply '+ (mapcar 'single-duration-minutes (split-string l)))))

Examples for the functions:

(single-duration-minutes "10:15-11:45") ; 90.0
(durations-HHMM "10:15-11:45 13:00-13:05 14:10-14:14") ; ("1:30" "0:05" "0:04")
(sum-durations "10:15-11:45 13:00-13:05 14:10-14:14") ; "1:39"

If you see an error or have any suggestions for improvement, please let me know.

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.