I wish to substract from a start time an end time in my org-mode spreadsheet. My times strings look like this (only the inline code):

  • starttime: 9:42 or 09:42
  • endtime: 18:20

So it's 24h format.

I wrote the following functions in my init.el:


;; NOTE: The following functions are used for time tracking in
;; org-mode spreadsheets! That is why they might be a bit specific.
(defun fill-time-tuple (time-tuple fill)
  (mapcar (lambda (x) (or x fill))

(defun seconds-to-hours-and-minutes (seconds)
  (let ((hours (/ seconds 3600)))
    (let ((remaining-seconds (- seconds (* 3600 hours))))
      (let ((minutes (/ remaining-seconds 60)))
        (list hours minutes)))))

(defun time-tuple-diff (time-tup-start time-tup-end)
    (time-subtract (apply #'encode-time (fill-time-tuple time-tup-start 0))
                   (apply #'encode-time (fill-time-tuple time-tup-end 0))))))

With this, I evaluated the functions and also restarted Emacs, to make sure the functions are defined. However, the following table still gives an error, when I try to make use of the functions:

| _ |       | starttime | updatetime | endtime | spenttime |
| # | times |      9:28 |      18:20 |    9:10 | #ERROR    |
#+TBLFM: @2$5=$updatetime::@2$6='(seconds-to-hours-and-minutes (time-tuple-diff (parse-time-string $endtime) (parse-time-string $starttime)))

Specifically, if I debug:

Substitution history of formula
Orig:   '(seconds-to-hours-and-minutes (time-tuple-diff (parse-time-string $endtime) (parse-time-string $starttime)))
$xyz->  '(seconds-to-hours-and-minutes (time-tuple-diff (parse-time-string 9:10) (parse-time-string 9:28)))
@r$c->  '(seconds-to-hours-and-minutes (time-tuple-diff (parse-time-string 9:10) (parse-time-string 9:28)))
$1->    '(seconds-to-hours-and-minutes (time-tuple-diff (parse-time-string 9:10) (parse-time-string 9:28)))
Result: #ERROR
Format: NONE
Final:  #ERROR

My guess is, that somehow the functions I defined are not available to spreadsheet calculation.

This is my guess, because the code actually works inside a source block:

#+BEGIN_SRC elisp
  (parse-time-string "10:30")
  (parse-time-string "09:20")))

| 1 | 10 |

How can I get my functions working for the spreadsheet formula evaluation?

Alternatively I could live with a simple GNU Calc formula to substract times. That is what I was initially looking for, but could not find. Then I switched to "OK I'll write an Elisp formula then!"

  • There are multiple gotchas here: note the 9:10 entry e.g. It is supposed to be a copy of 18:20 but it isn't. Calc interprets it as a ratio and simplifies it to lowest terms! There are similar potholes in all of this. You are probably better off using active dates in your table entries. See e.g. this question. – NickD May 28 '20 at 13:59
  • @NickD I tried that and you are right, much less cumbersome that way and I will go with that for now. If you made your comment into an answer, I could after some time, provided no one else solves this particular problem, accept it as an answer. – Zelphir Kaltstahl May 28 '20 at 17:07
  • 1
    BTW, you can also use passive dates (e.g. [2020-05-28 Thu 09:18]) as well as active dates (e.g. <2020-05-28 Thu 09:18>). The latter might cause problems if the file is e.g. an agenda file: you'll find all sorts of entries in your agenda! – NickD May 28 '20 at 17:29
  • As for an answer, I can describe some of the peculiarities that plague Org mode spreadsheets. Is that what you meant? Other than that, I don't think that there is anything wrong with your functions: it's just that the somewhat mysterious type conversions that Org mode and Calc do, make this approach unwieldy. So the answer is probably "Don't do that!" - use the methods described in the linked question (particularly in the answer by Tobias). – NickD May 28 '20 at 17:35

Your Answer

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

Browse other questions tagged or ask your own question.