# How to calculate time durations in tables?

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.

• 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. Commented Apr 9, 2021 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
Commented Apr 9, 2021 at 15:56

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.