0

I wish to calculate time string delta as below:

| date       | delta |
|------------+-------|
| 2018/12/28 |     0 |
| 2018/12/31 |    -1 |
#+TBLFM: $2=if(2==@#, 0, ($1 - @-1$1); %.0f

The expected answer should be 3 but now it's -1. I do not wish to change the time string format in table.

How can I modify the formula to covert timestring to datetime correctly?

  • Look at the first column of your table. What calc does is the computation 2018/(12*18) which gives 6.00595238096 as a float (dependent on the current calc-mode) and 2018/(12*31) giving 5.42473118281 as a float. The int-casted result is -1. – Tobias Sep 26 at 12:30
1

Just do the conversion into standard iso format and calc-eval in Elisp:

| date       | delta |
|------------+-------|
| 2018/12/28 |     0 |
| 2018/12/31 |     3 |
#+TBLFM: $2='(if (= @# 2) 0 (calc-eval (concat "<" (substitute ?- ?/ $1) ">-<" (substitute ?- ?/  @-1$1) ">") )); %.0f

You can cleanup the code by defining the following lisp function at an appropriate place (e.g., a source block in your org-file or your init files):

(defun my-orgtbl-time-diff (date1 date2)
  "Compute the day difference of two dates given in a format like 2019-09-23."
  (let* ((date-map (lambda (str)
                     (and (string-match "\\([0-9]\\{4\\}\\)/\\([0-9]\\{2\\}\\)/\\([0-9]\\{2\\}\\)" str)
                          (concat "<" (match-string 1 str) "-" (match-string 2 str) "-" (match-string 3 str) ">"))))
         (date1 (funcall date-map date1))
         (date2 (funcall date-map date2)))
    (if (and date1 date2)
        (calc-eval (concat date1 "-" date2))
      0)))

Employing that function your table formula has the following form.

| date       | delta |
|------------+-------|
| 2018/12/28 |     0 |
| 2018/12/31 |     3 |
#+TBLFM: $2='(my-orgtbl-time-diff $1 @-1$1)
1

Although you said you do not want to change the date format that is what you have to do to use the $1 syntax in the formula. For that to work, org mode functions will need to know that you are dealing with dates.

The first prerequisite is the brackets around the dates. Secondly, I had to go to full ISO format date for org to recognize the date. That might depend on my settings.

| date         | delta |
|--------------+-------|
| [2018-12-28] |     0 |
| [2018-12-31] |     3 |
#+TBLFM: $2=if(2==@#, 0, ($1 - @-1$1); %.0f

The alternative is to put together a very long formula using custom elisp to do the same thing.

  • 1
    The date field can be an active date <YYYY-MM-DD> or an inactive date [YYYY-MM-DD] but indeed it cannot be a bare date as the OP wants it: org mode will not recognize it. It also has to be an ISO date: org mode can be customized to output dates in other formats (generally as an overlay of some sort) but it only understands ISO dates on input. – NickD Dec 30 '18 at 12:45

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.